Hello,
So I have this below working code working:
%macro where_clause(where=);
%if &where.= %then %do;
%let where_optn= ;
%end;
%else %do;
%let where_optn=(where=(&where.)) ;
%end;
data class;
set sashelp.class &where_optn.;
run;
%mend;
%where_clause;
%where_clause(where=name="Alice" );
%where_clause(where=name="Alice" and sex="F" );
The same code would not run if I invoke the macro as:
%where_clause(where=name in ("Alice") );
Now I understand that it is because of parenthesis. I want the user to be able to pass more conditions using and/or.
Please advise.
Thanks
A simple solution that will work in most cases:
%macro where_clause(where=); %if %length(&where) %then %let where=(where=(&where)); data class; set sashelp.class &where; run; %mend;
You will need to consider all of the cases of the garbage your users may through at your macro.
%where_clause(where=%str(name="Alice") );
%str will keep some elements from being seen by the macro parser. But you will need to consider different approaches people are going to attempt to pass Macro elements such as % or & .
%nrstr will mask other characters from interpretation
Please see the documentation for these functions as well as %quote, %nrquote, %unquote and %superq and "Macro masking" This is not a trivial issue.
Does it have to be that complicated?
%macro where_clause(cFilter=);
%if &cFilter=%str() %then %let cFilter=1=1;
data class;
set sashelp.class (where=(&cFilter));
run;
%mend;
%where_clause(cFilter=);
%where_clause(cfilter=name="Alice" );
%where_clause(cFilter=name="Alice" and sex="F" );
I changed the parameter name to help avoid confusion both from a programmer side and in interpreting the model.
@ArpitSharma wrote:
This will fail for::
%where_clause(cFilter=name in ("Alice"));
Nope.
371 %where_clause(cfilter=name="Alice" );
NOTE: There were 1 observations read from the data set
SASHELP.CLASS.
WHERE name='Alice';
NOTE: The data set WORK.CLASS has 1 observations and 5 variables.'
NOTE: DATA statement used (Total process time):
real time 0.02 seconds
cpu time 0.01 seconds
EDIT: My bad, you're right.
It is not the SAS code that is failing it is the invalid %IF condition.
I would recode it this way. There are slightly more robust methods to test for empty parameters, but %LENGTH() works for 99% of real cases.
%macro where_clause(where=);
%local dsnopt;
%if %length(&where) %then %let dsnopt=where=(&where);
data class;
set sashelp.class (&dsnopt);
run;
%mend where_clause;
I don't understand your problem.
The code you posted should work as long as the users pass in valid syntax. You will not even need any macro quoting as anything that is valid inside of a WHERE=(....) dataset option will be valid to pass into the macro.
Do you have examples of things that do not work?
@Tom there's too many WHEREs in the code. In the parameter and the data step filter.
A simple solution that will work in most cases:
%macro where_clause(where=); %if %length(&where) %then %let where=(where=(&where)); data class; set sashelp.class &where; run; %mend;
@andreas_lds, because the macro variable WHERE is defined as a parameter to the macro, it is automatically local.
1 %let where=0; 2 %macro test(where=); 3 %put _user_; 4 %mend test; 5 6 %test(where=1) TEST WHERE 1 GLOBAL WHERE 0
I imagine some people might have the practice of declaring all macro variables that are local on a %LOCAL statement, including parameters, but it's not necessary to include the parameters.
Other than parameters, I'm a zealot about the %LOCAL statement. Nothings bugs me more than to see a post or worse yet paper where macro programmers ignore the need to define the scope of macro variables they create.
@Quentin wrote:
@andreas_lds, because the macro variable WHERE is defined as a parameter to the macro, it is automatically local.
Don't know why i haven't noticed that WHERE is a macro parameter. Thanks for clarifying my post.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.