Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
mp_derivedvars.test.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Testing mp_derivedvars macro
4
5 <h4> SAS Macros </h4>
6 @li mddl_derivedvars.sas
7 @li mf_existds.sas
8 @li mf_existvar.sas
9 @li mf_nobs.sas
10 @li mp_assert.sas
11 @li mp_assertcols.sas
12 @li mp_assertdsobs.sas
13 @li mp_assertscope.sas
14 @li mp_derivedvars.sas
15
16 @sasjslint {"maxLineLength": 81}
17**/
18
19/* the target table */
20data work.class;
21 set sashelp.class;
22run;
23
24/* the control table */
25%mddl_derivedvars(libds=work.dvcfg)
26
27proc sql;
28insert into work.dvcfg
29 set libref='WORK',dsn='CLASS',var_nm='band',var_deps='AGE'
30 ,var_type='C',var_len=8,var_fmt='',active=1
31 ,var_logic="ifc(AGE>13,'TEEN','JUNIOR')";
32insert into work.dvcfg
33 set libref='WORK',dsn='CLASS',var_nm='band_age',var_deps='BAND AGE'
34 ,var_type='C',var_len=16,var_fmt='',active=1
35 ,var_logic="cats(BAND,'-',AGE)";
36insert into work.dvcfg
37 set libref='WORK',dsn='CLASS',var_nm='double_age',var_deps='AGE'
38 ,var_type='N',var_len=8,var_fmt='',active=1
39 ,var_logic='AGE * 2';
40/* loaded first, so the dependency order cannot come from the row order */
41insert into work.dvcfg
42 set libref='WORK',dsn='CLASS',var_nm='band_label',var_deps='BAND SEX'
43 ,var_type='C',var_len=32,var_fmt='',active=1
44 ,var_logic="cats(BAND,'-',SEX)";
45/* inactive - should be excluded by the where clause */
46insert into work.dvcfg
47 set libref='WORK',dsn='CLASS',var_nm='ignored',var_deps=''
48 ,var_type='C',var_len=8,var_fmt='',active=0
49 ,var_logic="'X'";
50quit;
51
52/* the setup SQL above creates the SQL automatic macro variables, so the
53 snapshot is taken here - the check is for the macro's own leakage */
54%mp_assertscope(SNAPSHOT)
55
56%mp_derivedvars(
57 libds=work.class,
58 control=work.dvcfg,
59 where=%str(active=1),
60 outref=derref,
61 outview=work.vw_dv,
62 outvars=work.dvnames,
63 reserved=%str(_____DELETE__THIS__RECORD_____ TX_FROM TX_TO)
64)
65
66/* one row per variable, names upcased, inactive row excluded */
67%mp_assertdsobs(work.dvnames,
68 desc=Only the active variables are returned,
69 test=EQUALS 4,
70 outds=work.test_results
71)
72
73%mp_assertcols(work.vw_dv,
74 desc=The view carries the derived columns,
75 cols=BAND BAND_AGE DOUBLE_AGE BAND_LABEL,
76 test=ALL,
77 outds=work.test_results
78)
79
80%mp_assert(
81 iftrue=(%mf_existvar(work.vw_dv,IGNORED)=0),
82 desc=The inactive variable is not in the view,
83 outds=work.test_results
84)
85
86/* the values, including a variable built on another one */
87data work.chk;
88 set work.vw_dv;
89 where band='TEEN';
90 if band_age ne cats(band,'-',AGE) then output;
91 if double_age ne AGE*2 then output;
92 if band_label ne cats(band,'-',SEX) then output;
93run;
94
95%mp_assertdsobs(work.chk,
96 desc=Derived values and chained values are correct,
97 test=EQUALS 0,
98 outds=work.test_results
99)
100
101data work.chk2;
102 set work.vw_dv;
103 where band='TEEN';
104run;
105
106%mp_assertdsobs(work.chk2,
107 desc=A derived variable can be filtered on,
108 test=HASOBS,
109 outds=work.test_results
110)
111
112/* the label is the variable name plus the expression */
113proc contents noprint data=work.vw_dv out=work.vcols(keep=name label);
114run;
115
116data work.chklab;
117 set work.vcols;
118 where name='BAND' and label=:'BAND=ifc(AGE>13';
119run;
120
121%mp_assertdsobs(work.chklab,
122 desc=The label carries the variable name and the expression,
123 test=EQUALS 1,
124 outds=work.test_results
125)
126
127%mp_assert(
128 iftrue=("%mf_existds(libds=work.vw_dv)"="1"),
129 desc=The view was built,
130 outds=work.test_results
131)
132
133/* the statements are generated in dependency order, not in the order the
134 rows were loaded - BAND must be assigned before BAND_AGE */
135data work.stmts;
136 infile derref truncover;
137 input line $4000.;
138 seq=_n_;
139run;
140
141data work.chkord;
142 set work.stmts end=eof;
143 retain band_seq bandage_seq;
144 if prxmatch('/^BAND\s*=/',strip(line)) and missing(band_seq) then band_seq=seq;
145 if prxmatch('/^BAND_AGE\s*=/',strip(line)) and missing(bandage_seq)
146 then bandage_seq=seq;
147 if eof;
148 if missing(band_seq) or missing(bandage_seq) or band_seq>=bandage_seq
149 then output;
150run;
151
152%mp_assertdsobs(work.chkord,
153 desc=BAND is assigned before BAND_AGE whatever order the rows were loaded,
154 test=EQUALS 0,
155 outds=work.test_results
156)
157
158/* VAR_LABEL overrides the default label, and falls back when it is blank */
159%mddl_derivedvars(libds=work.dvcfg_label)
160
161proc sql;
162insert into work.dvcfg_label
163 set libref='WORK',dsn='CLASS',var_nm='band',var_deps='AGE'
164 ,var_type='C',var_len=8,var_fmt='',var_label='Age band'
165 ,var_logic="ifc(AGE>13,'TEEN','JUNIOR')",active=1;
166insert into work.dvcfg_label
167 set libref='WORK',dsn='CLASS',var_nm='double_age',var_deps='AGE'
168 ,var_type='N',var_len=8,var_fmt='',var_label=''
169 ,var_logic='AGE * 2',active=1;
170quit;
171
172%mp_derivedvars(
173 libds=work.class,
174 control=work.dvcfg_label,
175 outref=derref7,
176 outview=work.vw_label
177)
178
179/* assert on the generated statements - the label in the view can be changed
180 again by proc contents, the statement is what the macro emits */
181data work.stmts7;
182 infile derref7 truncover;
183 input line $4000.;
184run;
185
186data work.chklbl;
187 set work.stmts7 end=eof;
188 retain found_custom found_default;
189 if index(line,"label='Age band'")>0 then found_custom=1;
190 if index(line,"label='DOUBLE_AGE=AGE * 2'")>0 then found_default=1;
191 if eof;
192 if not found_custom or not found_default then output;
193run;
194
195%mp_assertdsobs(work.chklbl,
196 desc=VAR_LABEL is used when set and defaults when blank,
197 test=EQUALS 0,
198 outds=work.test_results
199)
200
201/* the default outvars name must not overwrite a control table that uses the
202 mddl_derivedvars default - this is the pair of defaults a caller is likely
203 to use */
204%mddl_derivedvars(libds=work.derived_vars)
205
206proc sql;
207insert into work.derived_vars
208 set libref='WORK',dsn='CLASS',var_nm='band',var_deps='AGE'
209 ,var_type='C',var_len=8,var_fmt='',active=1
210 ,var_logic="ifc(AGE>13,'TEEN','JUNIOR')";
211quit;
212
213%mp_derivedvars(
214 libds=work.class,
215 control=work.derived_vars,
216 outref=derref5,
217 outview=work.vw_default
218)
219
220%mp_assert(
221 iftrue=(0 < %mf_existvar(work.vw_default,BAND)),
222 desc=The default outvars does not overwrite the control table,
223 outds=work.test_results
224)
225
226%mp_assert(
227 iftrue=(%mf_nobs(work.derived_vars)=1),
228 desc=The control table survives the default outvars name,
229 outds=work.test_results
230)
231
232/* a filter containing quotes must not break the macro - a datetime literal
233 is the usual case, and quoting it again inside an %if condition does */
234%mp_derivedvars(
235 libds=work.class,
236 control=work.derived_vars,
237 where=%str(var_nm ne "NOPE"),
238 outref=derref11,
239 outvars=work.dvnames11,
240 outview=work.vw_wherequote
241)
242
243%mp_assertdsobs(work.dvnames11,
244 desc=A filter containing quotes is applied,
245 test=EQUALS 1,
246 outds=work.test_results
247)
248
249%mp_assert(
250 iftrue=(0 < %mf_existvar(work.vw_wherequote,BAND)),
251 desc=The view is still built when the filter contains quotes,
252 outds=work.test_results
253)
254
255/* a control table with no rows for the target leaves everything empty */
256%mddl_derivedvars(libds=work.dvcfg_empty)
257
258%mp_derivedvars(
259 libds=work.class,
260 control=work.dvcfg_empty,
261 outref=derref3,
262 outview=work.vw_empty,
263 outvars=work.dvnames3
264)
265
266%mp_assertdsobs(work.dvnames3,
267 desc=No rows in the control table means no variables,
268 test=EQUALS 0,
269 outds=work.test_results
270)
271
272%mp_assert(
273 iftrue=(%mf_existds(libds=work.vw_empty)=0),
274 desc=No view is built when there is nothing to derive,
275 outds=work.test_results
276)
277
278/* a missing control table is not an error, it just means no variables */
279%mp_derivedvars(
280 libds=work.class,
281 control=work.doesnotexist,
282 outref=derref4,
283 outvars=work.dvnames4
284)
285
286%mp_assertdsobs(work.dvnames4,
287 desc=A missing control table yields no variables,
288 test=EQUALS 0,
289 outds=work.test_results
290)
291
292/* a diamond graph - two variables built on one, then a fourth on both, then a
293 fifth on that. Rows are loaded in reverse name order so nothing but the
294 dependency sort can produce the right sequence */
295%mddl_derivedvars(libds=work.dvcfg_diamond)
296
297proc sql;
298insert into work.dvcfg_diamond
299 set libref='WORK',dsn='CLASS',var_nm='v5',var_deps='SEX V4'
300 ,var_type='C',var_len=40,var_fmt='',var_logic="cats(SEX,'-',put(V4,best12.))"
301 ,active=1;
302insert into work.dvcfg_diamond
303 set libref='WORK',dsn='CLASS',var_nm='v4',var_deps='V2 V3'
304 ,var_type='N',var_len=8,var_fmt='',var_logic='V2 + V3',active=1;
305insert into work.dvcfg_diamond
306 set libref='WORK',dsn='CLASS',var_nm='v3',var_deps='V1'
307 ,var_type='N',var_len=8,var_fmt='',var_logic='V1 + 1',active=1;
308insert into work.dvcfg_diamond
309 set libref='WORK',dsn='CLASS',var_nm='v2',var_deps='V1'
310 ,var_type='N',var_len=8,var_fmt='',var_logic='V1 * 2',active=1;
311insert into work.dvcfg_diamond
312 set libref='WORK',dsn='CLASS',var_nm='v1',var_deps='AGE'
313 ,var_type='N',var_len=8,var_fmt='',var_logic='AGE + 0',active=1;
314quit;
315
316%mp_derivedvars(
317 libds=work.class,
318 control=work.dvcfg_diamond,
319 outref=derref8,
320 outview=work.vw_diamond
321)
322
323%mp_assertcols(work.vw_diamond,
324 desc=The diamond graph is fully materialised,
325 cols=V1 V2 V3 V4 V5,
326 test=ALL,
327 outds=work.test_results
328)
329
330data work.chkdiamond;
331 set work.vw_diamond;
332 if v1 ne age then output;
333 if v2 ne age*2 then output;
334 if v3 ne age+1 then output;
335 if v4 ne age*3+1 then output;
336 if v5 ne cats(sex,'-',put(age*3+1,best12.)) then output;
337run;
338
339%mp_assertdsobs(work.chkdiamond,
340 desc=Every variable in the diamond graph resolves to the right value,
341 test=EQUALS 0,
342 outds=work.test_results
343)
344
345/* the assignment order must be v1, then v2 and v3, then v4, then v5 */
346data work.chkorder;
347 infile derref8 truncover;
348 input line $4000.;
349 seq=_n_;
350 length nm $32;
351 if prxmatch('/^V\d+\s*=/',strip(line)) then nm=scan(strip(line),1,'=');
352 else nm='';
353run;
354
355data work.chkorder2;
356 set work.chkorder end=eof;
357 retain s1 s2 s3 s4 s5;
358 if nm='V1' then s1=seq;
359 if nm='V2' then s2=seq;
360 if nm='V3' then s3=seq;
361 if nm='V4' then s4=seq;
362 if nm='V5' then s5=seq;
363 if eof;
364 if missing(s1) or missing(s2) or missing(s3) or missing(s4) or missing(s5)
365 then output;
366 else if s1 ge min(s2,s3) then output;
367 else if max(s2,s3) ge s4 then output;
368 else if s4 ge s5 then output;
369run;
370
371%mp_assertdsobs(work.chkorder2,
372 desc=The diamond graph is assigned in dependency order,
373 test=EQUALS 0,
374 outds=work.test_results
375)
376
377/* a label longer than the 256 limit, containing quotes, must still produce a
378 literal SAS can parse - the truncation cannot split a doubled quote */
379%mddl_derivedvars(libds=work.dvcfg_long)
380
381data work.longcfg;
382 length logic $4000;
383 logic=cats("ifc(AGE>13,'",repeat('a',300),"','short')");
384run;
385
386proc sql;
387insert into work.dvcfg_long
388 (libref,dsn,var_nm,var_deps,var_type,var_len,var_fmt,var_logic,active)
389 select 'WORK','CLASS','LONG_LABEL','AGE','C',400,'',logic,1
390 from work.longcfg;
391quit;
392
393%mp_derivedvars(
394 libds=work.class,
395 control=work.dvcfg_long,
396 outref=derref9,
397 outview=work.vw_long
398)
399
400data work.chkquotes;
401 infile derref9 truncover;
402 input line $4000.;
403 if index(line,"label='")>0 and mod(count(line,"'"),2) ne 0 then output;
404run;
405
406%mp_assertdsobs(work.chkquotes,
407 desc=A truncated label keeps an even quote count,
408 test=EQUALS 0,
409 outds=work.test_results
410)
411
412%mp_assert(
413 iftrue=(%mf_existds(libds=work.vw_long)=1),
414 desc=A label longer than the limit still builds the view,
415 outds=work.test_results
416)
417
418/* one control table can serve several tables - the LIBREF/DSN pair scopes the
419 definitions, so the same name means different things on each */
420data work.class2;
421 set sashelp.class;
422run;
423
424%mddl_derivedvars(libds=work.dvcfg_multi)
425
426proc sql;
427insert into work.dvcfg_multi
428 set libref='WORK',dsn='CLASS',var_nm='scaled',var_deps='AGE'
429 ,var_type='N',var_len=8,var_fmt='',var_logic='AGE + 100',active=1;
430insert into work.dvcfg_multi
431 set libref='WORK',dsn='CLASS2',var_nm='scaled',var_deps='AGE'
432 ,var_type='N',var_len=8,var_fmt='dollar12.2',var_logic='AGE + 200',active=1;
433quit;
434
435%mp_derivedvars(
436 libds=work.class,
437 control=work.dvcfg_multi,
438 outref=derref10,
439 outview=work.vw_multi1
440)
441
442%mp_derivedvars(
443 libds=work.class2,
444 control=work.dvcfg_multi,
445 outref=derref10,
446 outview=work.vw_multi2
447)
448
449data work.chkmulti;
450 merge work.vw_multi1(keep=name age scaled rename=(scaled=scaled1))
451 work.vw_multi2(keep=name age scaled rename=(scaled=scaled2));
452 by name;
453 if scaled1 ne age + 100 then output;
454 if scaled2 ne age + 200 then output;
455run;
456
457%mp_assertdsobs(work.chkmulti,
458 desc=The same variable name means different things per table,
459 test=EQUALS 0,
460 outds=work.test_results
461)
462
463/* a configured format is applied as given - asserted on the statement the
464 macro emits, which is what the view is built from */
465data work.chkfmt;
466 infile derref10 truncover;
467 input line $4000.;
468 if index(line,'format=DOLLAR12.2')>0 then output;
469run;
470
471%mp_assertdsobs(work.chkfmt,
472 desc=A configured format is applied,
473 test=EQUALS 1,
474 outds=work.test_results
475)
476
477/* VAR_LOGIC is inserted into a generated assignment, so anything that submits
478 code or reaches the file system or the macro symbol table has to be refused.
479 The character rules cannot see these payloads: BYTE(59) builds the semicolon
480 the source text must not contain. Each payload below therefore passes every
481 character check and must still be refused by name. */
482
483/* A DOSUBL program built out of BYTE(59) - no literal semicolon, ampersand,
484 percent or comment in the stored value */
485data work.payload;
486 length logic $4000;
487 logic="dosubl(cats('data _null_',byte(59),'data work.pwn',byte(59)
488 ,'run',byte(59)))";
489run;
490
491%mddl_derivedvars(libds=work.dvcfg_inject)
492
493proc sql;
494insert into work.dvcfg_inject
495 (libref,dsn,var_nm,var_deps,var_type,var_len,var_fmt,var_logic,active)
496 select 'WORK','CLASS','pwn','','N',8,'',logic,1 from work.payload;
497quit;
498
499%mp_derivedvars(
500 libds=work.class,
501 control=work.dvcfg_inject,
502 outref=drefi,
503 outview=work.vw_inj,
504 outvars=work.dvn_inj,
505 abort=NO,
506 errds=work.dv_inj_err
507)
508
509%mp_assertdsobs(work.dv_inj_err,
510 desc=A DOSUBL payload is refused even though it carries no semicolon,
511 test=HASOBS,
512 outds=work.test_results
513)
514
515%mp_assert(
516 iftrue=(%mf_existds(libds=work.vw_inj)=0),
517 desc=No view is built for a refused payload,
518 outds=work.test_results
519)
520
521/* the same for the file system and the macro symbol table */
522data work.payload2;
523 length logic $4000;
524 logic="fread(fopen('/etc/hostname'))";
525run;
526
527%mddl_derivedvars(libds=work.dvcfg_fread)
528
529proc sql;
530insert into work.dvcfg_fread
531 (libref,dsn,var_nm,var_deps,var_type,var_len,var_fmt,var_logic,active)
532 select 'WORK','CLASS','leak','','C',200,'',logic,1 from work.payload2;
533quit;
534
535%mp_derivedvars(
536 libds=work.class,
537 control=work.dvcfg_fread,
538 outref=dreff,
539 outview=work.vw_fr,
540 outvars=work.dvn_fr,
541 abort=NO,
542 errds=work.dv_fr_err
543)
544
545%mp_assertdsobs(work.dv_fr_err,
546 desc=A file read payload is refused,
547 test=HASOBS,
548 outds=work.test_results
549)
550
551data work.payload3;
552 length logic $4000;
553 logic="symputx('syscc',0)";
554run;
555
556%mddl_derivedvars(libds=work.dvcfg_symput)
557
558proc sql;
559insert into work.dvcfg_symput
560 (libref,dsn,var_nm,var_deps,var_type,var_len,var_fmt,var_logic,active)
561 select 'WORK','CLASS','sneaky','','N',8,'',logic,1 from work.payload3;
562quit;
563
564%mp_derivedvars(
565 libds=work.class,
566 control=work.dvcfg_symput,
567 outref=drefs,
568 outview=work.vw_sp,
569 outvars=work.dvn_sp,
570 abort=NO,
571 errds=work.dv_sp_err
572)
573
574%mp_assertdsobs(work.dv_sp_err,
575 desc=A macro symbol table write is refused,
576 test=HASOBS,
577 outds=work.test_results
578)
579
580/* a per row delay turns one read of the table into a denial of service */
581data work.payload4;
582 length logic $4000;
583 logic="sleep(10)";
584run;
585
586%mddl_derivedvars(libds=work.dvcfg_sleep)
587
588proc sql;
589insert into work.dvcfg_sleep
590 (libref,dsn,var_nm,var_deps,var_type,var_len,var_fmt,var_logic,active)
591select 'WORK','CLASS','slow','','N',8,'',logic,1 from work.payload4;
592quit;
593
594%mp_derivedvars(
595 libds=work.class,
596 control=work.dvcfg_sleep,
597 outref=drefz,
598 outview=work.vw_sl,
599 outvars=work.dvn_sl,
600 abort=NO,
601 errds=work.dv_sl_err
602)
603
604%mp_assertdsobs(work.dv_sl_err,
605 desc=A per row delay is refused,
606 test=HASOBS,
607 outds=work.test_results
608)
609
610/* the control: a refused word inside a quoted literal is data, not a call */
611data work.payload5;
612 length logic $4000;
613 logic="ifc(SEX='DOSUBL(','Y','N')";
614run;
615
616%mddl_derivedvars(libds=work.dvcfg_literal)
617
618proc sql;
619insert into work.dvcfg_literal
620 (libref,dsn,var_nm,var_deps,var_type,var_len,var_fmt,var_logic,active)
621 select 'WORK','CLASS','literal_ok','SEX','C',1,'',logic,1 from work.payload5;
622quit;
623
624%mp_derivedvars(
625 libds=work.class,
626 control=work.dvcfg_literal,
627 outref=drefc,
628 outview=work.vw_lit,
629 outvars=work.dvn_lit,
630 abort=NO,
631 errds=work.dv_lit_err
632)
633
634%mp_assertdsobs(work.dv_lit_err,
635 desc=A refused word inside a quoted literal is allowed,
636 test=EQUALS 0,
637 outds=work.test_results
638)
639
640%mp_assert(
641 iftrue=(0 < %mf_existvar(work.vw_lit,LITERAL_OK)),
642 desc=The literal control still builds its view,
643 outds=work.test_results
644)
645
646/* abort=NO reports every rule a definition breaks, not just the first: one
647 row references an undeclared derived variable, the other carries a bad
648 field, and both are returned in the same pass */
649%mddl_derivedvars(libds=work.dvcfg_multi)
650
651proc sql;
652insert into work.dvcfg_multi
653 set libref='WORK',dsn='CLASS',var_nm='aaa',var_deps=''
654 ,var_type='N',var_len=8,var_fmt='',var_logic='zzz+1',active=1;
655insert into work.dvcfg_multi
656 set libref='WORK',dsn='CLASS',var_nm='zzz',var_deps='AGE'
657 ,var_type='X',var_len=8,var_fmt='',var_logic='AGE',active=1;
658quit;
659
660%mp_derivedvars(
661 libds=work.class,
662 control=work.dvcfg_multi,
663 outref=drefm,
664 outview=work.vw_multi,
665 outvars=work.dvn_multi,
666 abort=NO,
667 errds=work.dv_multi_err
668)
669
670%mp_assertdsobs(work.dv_multi_err,
671 desc=abort NO reports every broken rule not just the first,
672 test=EQUALS 2,
673 outds=work.test_results
674)
675
676%mp_assert(
677 iftrue=(%mf_existds(libds=work.vw_multi)=0),
678 desc=abort NO builds no view when any rule fails,
679 outds=work.test_results
680)
681
682%mp_assertscope(COMPARE,ignorelist=_SASJS_WEBOUT_HEADERS,outds=work.test_results)