Macros for SAS Application Developers
https://github.com/sasjs/core
mp_assertcolvals.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Asserts the values in a column
4 @details Useful in the context of writing sasjs tests. The results of the
5 test are _appended_ to the &outds. table.
6
7 Example usage:
8
9 data work.checkds;
10 do checkval='Jane','James','Jill';
11 output;
12 end;
13 run;
14 %mp_assertcolvals(sashelp.class.name,
15 checkvals=work.checkds.checkval,
16 desc=At least one value has a match,
17 test=ANYVAL
18 )
19
20 data work.check;
21 do val='M','F';
22 output;
23 end;
24 run;
25 %mp_assertcolvals(sashelp.class.sex,
26 checkvals=work.check.val,
27 desc=All values have a match,
28 test=ALLVALS
29 )
30
31 <h4> SAS Macros </h4>
32 @li mf_existds.sas
33 @li mf_getuniquename.sas
34 @li mf_nobs.sas
35 @li mp_abort.sas
36
37
38 @param [in] indscol The input library.dataset.column to test for values
39 @param [in] checkvals= A library.dataset.column value containing a UNIQUE
40 list of values to be compared against the source (indscol).
41 @param [in] desc= (Testing observations) The user provided test description
42 @param [in] test= (ALLVALS) The test to apply. Valid values are:
43 @li ALLVALS - Test is a PASS if ALL values have a match in checkvals
44 @li ANYVAL - Test is a PASS if at least 1 value has a match in checkvals
45 @param [out] outds= (work.test_results) The output dataset to contain the
46 results. If it does not exist, it will be created, with the following format:
47 |TEST_DESCRIPTION:$256|TEST_RESULT:$4|TEST_COMMENTS:$256|
48 |---|---|---|
49 |User Provided description|PASS|Column &indscol contained ALL target vals|
50
51
52 <h4> Related Macros </h4>
53 @li mp_assertdsobs.sas
54
55 @version 9.2
56 @author Allan Bowe
57
58**/
59
60%macro mp_assertcolvals(indscol,
61 checkvals=0,
62 test=ALLVALS,
63 desc=mp_assertcolvals - no desc provided,
64 outds=work.test_results
65)/*/STORE SOURCE*/;
66
67 %mp_abort(iftrue= (&syscc ne 0)
68 ,mac=&sysmacroname
69 ,msg=%str(syscc=&syscc - on macro entry)
70 )
71
72 %local lib ds col clib cds ccol nobs;
73 %let lib=%scan(&indscol,1,%str(.));
74 %let ds=%scan(&indscol,2,%str(.));
75 %let col=%scan(&indscol,3,%str(.));
76 %mp_abort(iftrue= (%mf_existds(&lib..&ds)=0)
77 ,mac=&sysmacroname
78 ,msg=%str(&lib..&ds not found!)
79 )
80
81 %mp_abort(iftrue= (&checkvals=0)
82 ,mac=&sysmacroname
83 ,msg=%str(Set CHECKVALS to a library.dataset.column containing check vals)
84 )
85 %let clib=%scan(&checkvals,1,%str(.));
86 %let cds=%scan(&checkvals,2,%str(.));
87 %let ccol=%scan(&checkvals,3,%str(.));
88 %mp_abort(iftrue= (%mf_existds(&clib..&cds)=0)
89 ,mac=&sysmacroname
90 ,msg=%str(&clib..&cds not found!)
91 )
92 %let nobs=%mf_nobs(&clib..&cds);
93 %mp_abort(iftrue= (&nobs=0)
94 ,mac=&sysmacroname
95 ,msg=%str(&clib..&cds is empty!)
96 )
97
98 %let test=%upcase(&test);
99
100 %if &test ne ALLVALS and &test ne ANYVAL %then %do;
101 %mp_abort(
102 mac=&sysmacroname,
103 msg=%str(Invalid test - &test)
104 )
105 %end;
106
107 %local result orig;
108 %let result=-1;
109 %let orig=-1;
110 proc sql noprint;
111 select count(*) into: result
112 from &lib..&ds
113 where &col not in (
114 select &ccol from &clib..&cds
115 );
116 select count(*) into: orig from &lib..&ds;
117 quit;
118
119 %local notfound tmp1 tmp2;
120 %let tmp1=%mf_getuniquename();
121 %let tmp2=%mf_getuniquename();
122
123 /* this is a bit convoluted - but using sql outobs=10 throws warnings */
124 proc sql noprint;
125 create view &tmp1 as
126 select distinct &col
127 from &lib..&ds
128 where &col not in (
129 select &ccol from &clib..&cds
130 );
131 data &tmp2;
132 set &tmp1;
133 if _n_>10 then stop;
134 run;
135 proc sql;
136 select distinct &col into: notfound separated by ' ' from &tmp2;
137
138
139 %mp_abort(iftrue= (&syscc ne 0)
140 ,mac=&sysmacroname
141 ,msg=%str(syscc=&syscc after macro query)
142 )
143
144 data;
145 length test_description $256 test_result $4 test_comments $256;
146 test_description=symget('desc');
147 test_result='FAIL';
148 test_comments="&sysmacroname: &lib..&ds..&col has &result values "
149 !!"not in &clib..&cds..&ccol.. First 10 vals:"!!symget('notfound');
150 %if &test=ANYVAL %then %do;
151 if &result < &orig then test_result='PASS';
152 %end;
153 %else %if &test=ALLVALS %then %do;
154 if &result=0 then test_result='PASS';
155 %end;
156 %else %do;
157 test_comments="&sysmacroname: Unsatisfied test condition - &test";
158 %end;
159 run;
160
161 %local ds;
162 %let ds=&syslast;
163 proc append base=&outds data=&ds;
164 run;
165 proc sql;
166 drop table &ds;
167
168%mend mp_assertcolvals;