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= (0) 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  @li NOVAL - Test is a PASS if there are NO matches in checkvals
46  @param [out] outds= (work.test_results) The output dataset to contain the
47  results. If it does not exist, it will be created, with the following format:
48  |TEST_DESCRIPTION:$256|TEST_RESULT:$4|TEST_COMMENTS:$256|
49  |---|---|---|
50  |User Provided description|PASS|Column &indscol contained ALL target vals|
51 
52 
53  <h4> Related Macros </h4>
54  @li mp_assertdsobs.sas
55 
56  @version 9.2
57  @author Allan Bowe
58 
59 **/
60 
61 %macro mp_assertcolvals(indscol,
62  checkvals=0,
63  test=ALLVALS,
64  desc=mp_assertcolvals - no desc provided,
65  outds=work.test_results
66 )/*/STORE SOURCE*/;
67 
68  %mp_abort(iftrue= (&syscc ne 0)
69  ,mac=&sysmacroname
70  ,msg=%str(syscc=&syscc - on macro entry)
71  )
72 
73  %local lib ds col clib cds ccol nobs;
74  %let lib=%scan(&indscol,1,%str(.));
75  %let ds=%scan(&indscol,2,%str(.));
76  %let col=%scan(&indscol,3,%str(.));
77  %mp_abort(iftrue= (%mf_existds(&lib..&ds)=0)
78  ,mac=&sysmacroname
79  ,msg=%str(&lib..&ds not found!)
80  )
81 
82  %mp_abort(iftrue= (&checkvals=0)
83  ,mac=&sysmacroname
84  ,msg=%str(Set CHECKVALS to a library.dataset.column containing check vals)
85  )
86  %let clib=%scan(&checkvals,1,%str(.));
87  %let cds=%scan(&checkvals,2,%str(.));
88  %let ccol=%scan(&checkvals,3,%str(.));
89  %mp_abort(iftrue= (%mf_existds(&clib..&cds)=0)
90  ,mac=&sysmacroname
91  ,msg=%str(&clib..&cds not found!)
92  )
93  %let nobs=%mf_nobs(&clib..&cds);
94  %mp_abort(iftrue= (&nobs=0)
95  ,mac=&sysmacroname
96  ,msg=%str(&clib..&cds is empty!)
97  )
98 
99  %let test=%upcase(&test);
100 
101  %if &test ne ALLVALS and &test ne ANYVAL and &test ne NOVAL %then %do;
102  %mp_abort(
103  mac=&sysmacroname,
104  msg=%str(Invalid test - &test)
105  )
106  %end;
107 
108  %local result orig;
109  %let result=-1;
110  %let orig=-1;
111  proc sql noprint;
112  select count(*) into: result trimmed
113  from &lib..&ds
114  where &col not in (
115  select &ccol from &clib..&cds
116  );
117  select count(*) into: orig trimmed from &lib..&ds;
118  quit;
119 
120  %local notfound tmp1 tmp2;
121  %let tmp1=%mf_getuniquename();
122  %let tmp2=%mf_getuniquename();
123 
124  /* this is a bit convoluted - but using sql outobs=10 throws warnings */
125  proc sql noprint;
126  create view &tmp1 as
127  select distinct &col
128  from &lib..&ds
129  where &col not in (
130  select &ccol from &clib..&cds
131  );
132  data &tmp2;
133  set &tmp1;
134  if _n_>10 then stop;
135  run;
136  proc sql;
137  select distinct &col into: notfound separated by ' ' from &tmp2;
138 
139 
140  %mp_abort(iftrue= (&syscc ne 0)
141  ,mac=&sysmacroname
142  ,msg=%str(syscc=&syscc after macro query)
143  )
144 
145  data;
146  length test_description $256 test_result $4 test_comments $256;
147  test_description=symget('desc');
148  test_result='FAIL';
149  test_comments="&sysmacroname: &lib..&ds..&col has &result/&orig values "
150  !!"not in &clib..&cds..&ccol.. First 10 vals:"!!symget('notfound');
151  %if &test=ANYVAL %then %do;
152  if &result < &orig then test_result='PASS';
153  %end;
154  %else %if &test=ALLVALS %then %do;
155  if &result=0 then test_result='PASS';
156  %end;
157  %else %if &test=NOVAL %then %do;
158  if &result=&orig then test_result='PASS';
159  %end;
160  %else %do;
161  test_comments="&sysmacroname: Unsatisfied test condition - &test";
162  %end;
163  run;
164 
165  %local ds;
166  %let ds=&syslast;
167  proc append base=&outds data=&ds;
168  run;
169  proc sql;
170  drop table &ds;
171 
172 %mend mp_assertcolvals;