Macros for SAS Application Developers
https://github.com/sasjs/core
mp_filtergenerate.sas File Reference

Generates a filter clause from an input table, to a fileref. More...

Go to the source code of this file.

Detailed Description

Uses the input table to generate an output filter clause. This feature is used to create dynamic dropdowns in Data Controller for SAS&reg. The input table should be in the format below:

GROUP_LOGIC:$3 SUBGROUP_LOGIC:$3 SUBGROUP_ID:8. VARIABLE_NM:$32 OPERATOR_NM:$10 RAW_VALUE:$4000
AND AND 1 AGE = 12
AND AND 1 SEX <= 'M'
AND OR 2 Name NOT IN ('Jane','Alfred')
AND OR 2 Weight >= 7

Note - if the above table is received from an external client, the values should first be validated using the mp_filtercheck.sas macro to avoid risk of SQL injection.

To generate the filter, run the following code:

data work.filtertable;
  infile datalines4 dsd;
  input GROUP_LOGIC:$3. SUBGROUP_LOGIC:$3. SUBGROUP_ID:8. VARIABLE_NM:$32.
    OPERATOR_NM:$10. RAW_VALUE:$4000.;
datalines4;
AND,AND,1,AGE,=,12
AND,AND,1,SEX,<=,"'M'"
AND,OR,2,Name,NOT IN,"('Jane','Alfred')"
AND,OR,2,Weight,>=,7
;;;;
run;

%mp_filtergenerate(work.filtertable,outref=myfilter)

data _null_;
  infile myfilter;
  input;
  put _infile_;
run;

Will write the following query to the log:

( AGE = 12 AND SEX <= 'M' ) AND ( Name NOT IN ('Jane','Alfred') OR Weight >= 7 )

Parameters
[in]indsThe input table with query values
[out]outref=(filter) The output fileref to contain the filter clause. Will be created (or replaced).

Related Macros

SAS Macros

Version
9.3
Author
Allan Bowe

Definition in file mp_filtergenerate.sas.