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

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]	inds	The 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.