Production Ready Macros for SAS Application Developers
https://github.com/sasjs/core
mp_filtergenerate.sas
Go to the documentation of this file.
1/**
2 @file
3 @brief Generates a filter clause from an input table, to a fileref
4 @details Uses the input table to generate an output filter clause.
5 This feature is used to create dynamic dropdowns in [Data Controller for SAS&reg](
6 https://datacontroller.io). The input table should be in the format below:
7
8 |GROUP_LOGIC:$3|SUBGROUP_LOGIC:$3|SUBGROUP_ID:8.|VARIABLE_NM:$32|OPERATOR_NM:$10|RAW_VALUE:$4000|
9 |---|---|---|---|---|---|
10 |AND|AND|1|AGE|=|12|
11 |AND|AND|1|SEX|<=|'M'|
12 |AND|OR|2|Name|NOT IN|('Jane','Alfred')|
13 |AND|OR|2|Weight|>=|7|
14
15 Note - if the above table is received from an external client, the values
16 should first be validated using the mp_filtercheck.sas macro to avoid risk
17 of SQL injection.
18
19 To generate the filter, run the following code:
20
21 data work.filtertable;
22 infile datalines4 dsd;
23 input GROUP_LOGIC:$3. SUBGROUP_LOGIC:$3. SUBGROUP_ID:8. VARIABLE_NM:$32.
24 OPERATOR_NM:$10. RAW_VALUE:$4000.;
25 datalines4;
26 AND,AND,1,AGE,=,12
27 AND,AND,1,SEX,<=,"'M'"
28 AND,OR,2,Name,NOT IN,"('Jane','Alfred')"
29 AND,OR,2,Weight,>=,7
30 ;;;;
31 run;
32
33 %mp_filtergenerate(work.filtertable,outref=myfilter)
34
35 data _null_;
36 infile myfilter;
37 input;
38 put _infile_;
39 run;
40
41 Will write the following query to the log:
42
43 > (
44 > AGE = 12
45 > AND
46 > SEX <= 'M'
47 > ) AND (
48 > Name NOT IN ('Jane','Alfred')
49 > OR
50 > Weight >= 7
51 > )
52
53 @param [in] inds The input table with query values
54 @param [out] outref= The output fileref to contain the filter clause. Will
55 be created (or replaced).
56
57 <h4> Related Macros </h4>
58 @li mp_filtercheck.sas
59 @li mp_filtervalidate.sas
60
61 <h4> SAS Macros </h4>
62 @li mp_abort.sas
63 @li mf_nobs.sas
64
65 @version 9.3
66 @author Allan Bowe
67
68**/
69
70%macro mp_filtergenerate(inds,outref=filter);
71
72%mp_abort(iftrue= (&syscc ne 0)
73 ,mac=&sysmacroname
74 ,msg=%str(syscc=&syscc - on macro entry)
75)
76
77filename &outref temp;
78
79%if %mf_nobs(&inds)=0 %then %do;
80 /* ensure we have a default filter */
81 data _null_;
82 file &outref;
83 put '1=1';
84 run;
85%end;
86%else %do;
87 data _null_;
88 file &outref lrecl=32800;
89 set &inds end=last;
90 by SUBGROUP_ID;
91 if _n_=1 then put '((';
92 else if first.SUBGROUP_ID then put +1 GROUP_LOGIC '(';
93 else put +2 SUBGROUP_LOGIC;
94
95 put +4 VARIABLE_NM OPERATOR_NM RAW_VALUE;
96
97 if last.SUBGROUP_ID then put ')'@;
98 if last then put ')';
99 run;
100%end;
101
102%mend mp_filtergenerate;