Macros for SAS Application Developers
https://github.com/sasjs/core
Loading...
Searching...
No Matches
mp_filtercheck.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Checks an input filter table for validity
4 @details Performs checks on the input table to ensure it arrives in the
5 correct format. This is necessary to prevent code injection. Will update
6 SYSCC to 1008 if bad records are found, and call mp_abort.sas for a
7 graceful service exit (configurable).
8
9 Used for dynamic filtering in [Data Controller for SAS®](
10 https://datacontroller.io).
11
12 Usage:
13
14 %mp_filtercheck(work.filter,targetds=sashelp.class,outds=work.badrecords)
15
16 The input table should have the following format:
17
18 |GROUP_LOGIC:$3|SUBGROUP_LOGIC:$3|SUBGROUP_ID:8.|VARIABLE_NM:$32|OPERATOR_NM:$10|RAW_VALUE:$4000|
19 |---|---|---|---|---|---|
20 |AND|AND|1|AGE|=|12|
21 |AND|AND|1|SEX|<=|'M'|
22 |AND|OR|2|Name|NOT IN|('Jane','Alfred')|
23 |AND|OR|2|Weight|>=|7|
24
25 Rules applied:
26
27 @li GROUP_LOGIC - only AND/OR
28 @li SUBGROUP_LOGIC - only AND/OR
29 @li SUBGROUP_ID - only integers
30 @li VARIABLE_NM - must be in the target table
31 @li OPERATOR_NM - only =/>/</<=/>=/BETWEEN/IN/NOT IN/NE/CONTAINS
32 @li RAW_VALUE - no unquoted values except integers, commas and spaces. For
33 numeric columns, SAS date, datetime and time literals are also accepted, eg
34 '20APR2042'd, '01JAN1960:00:00:42'dt, '00:00:42't.
35
36 @returns The &outds table containing any bad rows, plus a REASON_CD column.
37
38 @param [in] inds The table to be checked, with the format above
39 @param [in] targetds= The target dataset against which to verify VARIABLE_NM.
40 This must be available (ie, the library must be assigned).
41 @param [out] abort= (YES) If YES will call mp_abort.sas on any exceptions
42 @param [out] outds= (work.badrecords) The output table, which is a copy of the
43 &inds. table plus a REASON_CD column, containing only bad records.
44 If bad records are found, the SYSCC value will be set to 1008
45 (a general data problem).
46 Downstream processes should check this table (and return code) before
47 continuing.
48
49 <h4> SAS Macros </h4>
50 @li mp_abort.sas
51 @li mf_getuniquefileref.sas
52 @li mf_getvarlist.sas
53 @li mf_getvartype.sas
54 @li mp_filtergenerate.sas
55 @li mp_filtervalidate.sas
56
57 <h4> Related Macros </h4>
58 @li mp_filtergenerate.sas
59 @li mp_filtervalidate.sas
60
61 @version 9.3
62 @author Allan Bowe
63
64 @todo Support hex / name literals and exponents in RAW_VALUE field
65**/
66
67%macro mp_filtercheck(inds,targetds=,outds=work.badrecords,abort=YES);
68
69%mp_abort(iftrue= (&syscc ne 0)
70 ,mac=&sysmacroname
71 ,msg=%str(syscc=&syscc - on macro entry)
72)
73
74/* Validate input column */
75%local vtype;
76%let vtype=%mf_getvartype(&inds,RAW_VALUE);
77%mp_abort(iftrue=(&abort=YES and &vtype ne C),
78 mac=&sysmacroname,
79 msg=%str(%str(ERR)OR: RAW_VALUE must be character)
80)
81%if &vtype ne C %then %do;
82 %put &sysmacroname: RAW_VALUE must be character;
83 %let syscc=42;
84 %return;
85%end;
86
87
88/**
89 * Sanitise the values based on valid value lists, then strip out
90 * quotes, commas, periods and spaces.
91 */
92%local reason_cd nobs;
93%let nobs=0;
94data &outds;
95 /*length GROUP_LOGIC SUBGROUP_LOGIC $3 SUBGROUP_ID 8 VARIABLE_NM $32
96 OPERATOR_NM $10 RAW_VALUE $4000;*/
97 set &inds end=last;
98 length reason_cd $4032 vtype vtype2 $1 vnum dsid 8 tmp $4000 re_temporal 8;
99 drop tmp re_temporal;
100 retain re_temporal;
101 /* SAS temporal literals - date, datetime and time */
102 if _n_=1 then re_temporal=prxparse("/^'[^']*'(dt|d|t)$/i");
103
104 /* quick check to ensure column exists */
105 if upcase(VARIABLE_NM) not in
106 (%upcase(%mf_getvarlist(&targetds,dlm=%str(,),quote=SINGLE)))
107 then do;
108 REASON_CD="Variable "!!cats(variable_nm)!!" not in &targetds";
109 putlog REASON_CD= VARIABLE_NM=;
110 call symputx('reason_cd',reason_cd,'l');
111 call symputx('nobs',_n_,'l');
112 output;
113 return;
114 end;
115
116 /* need to open the dataset to get the column type */
117 retain dsid;
118 if _n_=1 then dsid=open("&targetds","i");
119 if dsid>0 then do;
120 vnum=varnum(dsid,VARIABLE_NM);
121 if vnum<1 then do;
122 /* should not happen as was also tested for above */
123 REASON_CD=cats("Variable (",VARIABLE_NM,") not found in &targetds");
124 putlog REASON_CD= dsid=;
125 call symputx('reason_cd',reason_cd,'l');
126 call symputx('nobs',_n_,'l');
127 output;
128 goto endstep;
129 end;
130 /* now we can get the type */
131 else vtype=vartype(dsid,vnum);
132 end;
133 else do;
134 REASON_CD=cats("Could not open &targetds");
135 putlog REASON_CD= dsid=;
136 call symputx('reason_cd',reason_cd,'l');
137 call symputx('nobs',_n_,'l');
138 output;
139 stop;
140 end;
141
142 /* closed list checks */
143 if GROUP_LOGIC not in ('AND','OR') then do;
144 REASON_CD='GROUP_LOGIC should be AND/OR, not:'!!cats(GROUP_LOGIC);
145 putlog REASON_CD= GROUP_LOGIC=;
146 call symputx('reason_cd',reason_cd,'l');
147 call symputx('nobs',_n_,'l');
148 output;
149 end;
150 if SUBGROUP_LOGIC not in ('AND','OR') then do;
151 REASON_CD='SUBGROUP_LOGIC should be AND/OR, not:'!!cats(SUBGROUP_LOGIC);
152 putlog REASON_CD= SUBGROUP_LOGIC=;
153 call symputx('reason_cd',reason_cd,'l');
154 call symputx('nobs',_n_,'l');
155 output;
156 end;
157 if mod(SUBGROUP_ID,1) ne 0 then do;
158 REASON_CD='SUBGROUP_ID should be integer, not '!!cats(subgroup_id);
159 putlog REASON_CD= SUBGROUP_ID=;
160 call symputx('reason_cd',reason_cd,'l');
161 call symputx('nobs',_n_,'l');
162 output;
163 end;
164 if OPERATOR_NM not in
165 ('=','>','<','<=','>=','NE','GE','LE','BETWEEN','IN','NOT IN','CONTAINS')
166 then do;
167 REASON_CD='Invalid OPERATOR_NM: '!!cats(OPERATOR_NM);
168 putlog REASON_CD= OPERATOR_NM=;
169 call symputx('reason_cd',reason_cd,'l');
170 call symputx('nobs',_n_,'l');
171 output;
172 end;
173
174 /* special missing logic */
175 if vtype='N' & OPERATOR_NM in ('=','>','<','<=','>=','NE','GE','LE') then do;
176 if cats(upcase(raw_value)) in (
177 '.','.A','.B','.C','.D','.E','.F','.G','.H','.I','.J','.K','.L','.M','.N'
178 '.N','.O','.P','.Q','.R','.S','.T','.U','.V','.W','.X','.Y','.Z','._'
179 )
180 then do;
181 /* valid numeric - exit data step loop */
182 return;
183 end;
184 else if subpad(upcase(raw_value),1,1) in (
185 'A','B','C','D','E','F','G','H','I','J','K','L','M','N'
186 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z','_'
187 )
188 then do;
189 /* check if the raw_value contains a valid variable NAME */
190 vnum=varnum(dsid,subpad(raw_value,1,32));
191 if vnum>0 then do;
192 /* now we can get the type */
193 vtype2=vartype(dsid,vnum);
194 /* check type matches */
195 if vtype2=vtype then do;
196 /* valid target var - exit loop */
197 return;
198 end;
199 else do;
200 REASON_CD=cats("Compared Type (",vtype2,") is not (",vtype,")");
201 putlog REASON_CD= dsid=;
202 call symputx('reason_cd',reason_cd,'l');
203 call symputx('nobs',_n_,'l');
204 output;
205 goto endstep;
206 end;
207 end;
208 end;
209 end;
210
211 /* a SAS temporal literal is a valid value for a numeric column */
212 if vtype='N' and prxmatch(re_temporal,cats(raw_value))>0 then return;
213
214 /* special logic */
215 if OPERATOR_NM in ('IN','NOT IN','BETWEEN') then do;
216 if OPERATOR_NM='BETWEEN' then raw_value1=tranwrd(raw_value,' AND ',',');
217 else do;
218 if substr(raw_value,1,1) ne '('
219 or substr(cats(reverse(raw_value)),1,1) ne ')'
220 then do;
221 REASON_CD='Missing start/end bracket in RAW_VALUE';
222 putlog REASON_CD= OPERATOR_NM= raw_value= raw_value1= ;
223 call symputx('reason_cd',reason_cd,'l');
224 call symputx('nobs',_n_,'l');
225 output;
226 end;
227 else raw_value1=substr(raw_value,2,max(length(raw_value)-2,0));
228 end;
229 /* we now have a comma seperated list of values */
230 if vtype='N' then do i=1 to countc(raw_value1, ',')+1;
231 tmp=scan(raw_value1,i,',');
232 if cats(tmp) ne '.' and input(tmp, ?? 8.) eq . then do;
233 /* a SAS temporal literal is a valid value for a numeric column */
234 if prxmatch(re_temporal,cats(tmp))=0 then do;
235 if OPERATOR_NM ='BETWEEN' and subpad(upcase(tmp),1,1) in (
236 'A','B','C','D','E','F','G','H','I','J','K','L','M','N'
237 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z','_'
238 )
239 then do;
240 /* check if the raw_value contains a valid variable NAME */
241 /* is not valid syntax for IN or NOT IN */
242 vnum=varnum(dsid,subpad(tmp,1,32));
243 if vnum>0 then do;
244 /* now we can get the type */
245 vtype2=vartype(dsid,vnum);
246 /* check type matches */
247 if vtype2 ne vtype then do;
248 REASON_CD=cats("Compared Type (",vtype2,") is not (",vtype,")");
249 putlog REASON_CD= dsid=;
250 call symputx('reason_cd',reason_cd,'l');
251 call symputx('nobs',_n_,'l');
252 output;
253 goto endstep;
254 end;
255 end;
256 else do;
257 REASON_CD='Non Numeric value provided';
258 putlog REASON_CD= OPERATOR_NM= raw_value= raw_value1= ;
259 call symputx('reason_cd',reason_cd,'l');
260 call symputx('nobs',_n_,'l');
261 output;
262 end;
263 end;
264 else do;
265 REASON_CD='Non Numeric value provided';
266 putlog REASON_CD= OPERATOR_NM= raw_value= raw_value1= ;
267 call symputx('reason_cd',reason_cd,'l');
268 call symputx('nobs',_n_,'l');
269 output;
270 end;
271 end;
272 end;
273 end;
274 /* every value of a numeric IN / BETWEEN list is checked one by one above,
275 so a numeric list skips the generic check below - that would only flag
276 the list syntax itself. A character list is unchanged. */
277 if vtype='N' then return;
278 end;
279 else raw_value1=raw_value;
280
281 /* remove nested literals eg '' */
282 raw_value1=tranwrd(raw_value1,"''",'');
283
284 /* now match string literals (always single quotes) */
285 raw_value2=raw_value1;
286 regex = prxparse("s/(\').*?(\')//");
287 call prxchange(regex,-1,raw_value2);
288
289 /* remove commas and periods*/
290 raw_value3=compress(raw_value2,',.');
291
292 /* output records that contain values other than digits and spaces */
293 if notdigit(compress(raw_value3,' '))>0 then do;
294 if vtype='C' and subpad(upcase(raw_value),1,1) in (
295 'A','B','C','D','E','F','G','H','I','J','K','L','M','N'
296 'N','O','P','Q','R','S','T','U','V','W','X','Y','Z','_'
297 )
298 then do;
299 /* check if the raw_value contains a valid variable NAME */
300 vnum=varnum(dsid,subpad(raw_value,1,32));
301 if vnum>0 then do;
302 /* now we can get the type */
303 vtype2=vartype(dsid,vnum);
304 /* check type matches */
305 if vtype2=vtype then do;
306 /* valid target var - exit loop */
307 return;
308 end;
309 else do;
310 REASON_CD=cats("Compared Char Type (",vtype2,") is not (",vtype,")");
311 putlog REASON_CD= dsid=;
312 call symputx('reason_cd',reason_cd,'l');
313 call symputx('nobs',_n_,'l');
314 output;
315 goto endstep;
316 end;
317 end;
318 end;
319
320 putlog raw_value3= $hex32.;
321 REASON_CD=cats('Invalid RAW_VALUE:',raw_value);
322 putlog (_all_)(=);
323 call symputx('reason_cd',reason_cd,'l');
324 call symputx('nobs',_n_,'l');
325 output;
326 end;
327
328 endstep:
329 if last then rc=close(dsid);
330run;
331
332
333data _null_;
334 set &outds end=last;
335 putlog (_all_)(=);
336run;
337
338%mp_abort(iftrue=(&abort=YES and &nobs>0),
339 mac=&sysmacroname,
340 msg=%str(Data issue: %superq(reason_cd))
341)
342
343%if &nobs>0 %then %do;
344 %let syscc=1008;
345 %return;
346%end;
347
348/**
349 * syntax checking passed but it does not mean the filter is valid
350 * for that we can run a proc sql validate query
351 */
352%local fref1;
353%let fref1=%mf_getuniquefileref();
354%mp_filtergenerate(&inds,outref=&fref1)
355
356/* this macro will also set syscc to 1008 if any issues found */
357%mp_filtervalidate(&fref1,&targetds,outds=&outds,abort=&abort)
358
359%mend mp_filtercheck;