How to handle multiple filters in SAS stored process

Question

I'm creating a SAS stored process report in which user can filter data using different cafeterias.

This is what I'm doing

%let ID_WHERE_CLAUSE=; 
%let Source = "group";
%let ActionRequired = "Daily";


%macro SetFilters;

data _null_; 

%if &&ActionRequired ne "A" %then %do;
%let test = "ActionR";
    call symputx('ID_WHERE_CLAUSE',cats(' and ActionRequired = ',' &ActionRequired',''));
%end;


%if &&Source ne "A" %then %do;
%let test = "Source";
    call symputx('ID_WHERE_CLAUSE',cats('and Source = ',' &Source',''));
%end;

run;

%mend; %SetFilters;

%put &ID_WHERE_CLAUSE;

in ID_WHERE_CLAUSE data should be as and action required = "Daily" and source = "group" but my code is only appending last filter to my ID_WHERE_CLAUSE variable as and source = "group"

my expected result is and action required = "Daily" and source = "group"

but actual result I'm getting is and source = "group"

That's why I'm not getting my expected result. How can I append all where conditions in my ID_WHERE_CLAUSE variable.

I tried this too but didn't work either.

call symputx('ID_WHERE_CLAUSE',' and ActionRequired =  &ActionRequired','');
call symputx('ID_WHERE_CLAUSE', ' and Source =  &Source');

I'm using it as

proc sql noprint;
create table filter_data as
select * from data 
where 1=1 &ID_WHERE_CLAUSE;
quit;

Kindly tell me how can i concatenate all where clause to it or any better approach.

user667489 user667489 · Accepted Answer · 2018-03-01T09:39:03

call symputx does not append to macro variables - it overwrites them. So you need to capture the existing value as part of the new value you want to set. E.g.

call symputx('ID_WHERE_CLAUSE',cats(symget("ID_WHERE_CLAUSE"),'and Source = ',' &Source',''));

You need to use symget to do this in your scenario rather than referencing the macro variable directly, otherwise it may be resolved before your earlier data step executes and sets the initial value.

How to handle multiple filters in SAS stored process

1 Answers