%macro test(var1=);
sql--statements;
where id in (&var1);
%mend;
%test(var1=);
The user can pass one or more var1. How to pass the values into where clause with quotes around them?
;puI usually start with
%test (var1=%str('a' 'b'));
and see if that works.
I don't think you need macro quoting (the %STR function) in this case.
%test(var1='a' 'b')
should work.
Also, I would suggest you change the name of the parameter. I think var1= suggests that the user should pass in the name of a variable. In this case, the user is passing in a value, not a variable. So you might want to call it value1= or IDlist= or something like that.
HTH,
-Q.
Here are a few options.
1) Put the () needed for the IN operator into the value passed into the macro variable. This will allow you to use commas without confusing SAS.
%macro test1(idlist=);
data _null_;
set sashelp.class;
where name in &idlist ;
put (_all_) (=);
run;
%mend test1;
%test1(idlist=('Alfred','Alice') )
2) It the values cannot contain spaces then just pass the variables as space delimited instead of comma delimited. Add the commas in the macro.
%macro test2(idlist=);
%let idlist=%sysfunc(tranwrd(%sysfunc(compbl(&idlist)),%str( ),%str(,))) ;
data _null_;
set sashelp.class;
where name in (&idlist) ;
put (_all_) (=);
run;
%mend test2;
%test2(idlist='Alfred' 'Alice' )
3) Same as (2) but also add the quotes.
%macro test3(idlist=);
%let idlist=%sysfunc(tranwrd(%str(%')%sysfunc(compbl(&idlist))%str(%'),%str( ),',')) ;
data _null_;
set sashelp.class;
where name in (&idlist) ;
put (_all_) (=);
run;
%mend test3;
%test3(idlist=Alfred Alice);
Thanks Tom.The whole idea is user will provide the input from front end.There is a chance s/he may select one value for var1 or multiple values.And also I need to make sure the code works if he is passing another variable var2. So the situation could be he may choose to pass var1 and var2 or either of these two.
Then just amend Tom's code accordingly. Below a sample how this could look like.
options mprint;
%macro test2(var1=,var2=);
%if &var1 ne %then %let var1=%sysfunc(tranwrd(%sysfunc(compbl(&var1)),%str( ),%str(,))) ;
%if &var2 ne %then %let var2=%sysfunc(tranwrd(%sysfunc(compbl(&var2)),%str( ),%str(,))) ;
data _null_;
set sashelp.class;
%if (&var1&var2) ne %then %str(where);
%if &var1 ne %then %str(name in (&var1)) ;
%if &var1 ne and &var2 ne %then %str(and);
%if &var2 ne %then %str(sex in (&var2)) ;
%str(;)
put (_all_) (=);
run;
%mend test2;
%test2(var1='Alfred' 'Alice' )
%test2(var1='Alfred' 'Alice',var2='M' )
%test2(var2='M' )
%test2();
Hi,
You need to make it clearer: is it one, two, or "multiple values". If two, just use two parameters as above. If it's "multiple values", where the number could vary, review the doc for parmbuff.
SAS(R) 9.2 Macro Language: Reference
Scott
%macro test(var1=,var2=);
sql--statements;
where id in (&var1);
where ssn in (&var2);
%mend;
%test(var1=, var2=);
The user can pass one parameter var1 with one or more values OR he can pass multiple parameters var1 var2 .. with one or more values to var1 /var2.
So if only var1 is passed then the where condition must work only for those values of var1.
if var2 is passed where condition must pass for var2 values.
For example..if var1 is id then where id in (....). the other where condition must not be executed.
if var2 is ssn, only where ssn in (....), must be executed.
OK, you say:
Sorry for being dense, but let me reword that 2nd statement to make sure I've got it right:
He can pass two parameters var1 var2, with one or more values to var1 and var2.
You then say the var2 parameter has precedence over the var1 parameter, if defined (non-blank).
Assuming this is correct, does this work for you?
Go here: SAS Macros - by Richard A. DeVenezia - seplist.sas, copy %seplist, and compile the macro. Then submit the following code:
%macro sasphile(var1=, var2=);
%* var1 and var2 should be space separated lists ;
%local filter;
%if (%superq(var2) ne ) %then %do;
%let filter = age in (%seplist(&var2));
%end;
%else
%if (%superq(var1) ne ) %then %do;
%let var1=%upcase(&var1);
%let filter = upcase(name) in (%seplist(&var1,nest=qq));
%let filter = %unquote(&filter);
%end;
proc sql;
create table test as
select * from sashelp.class
%if (%superq(filter) ne ) %then %do;
where &filter
%end;
;
quit;
proc print;
run;
%mend;
options mprint;
%sasphile(var1=alfred henry joyce robert)
%sasphile(var1=alfred henry joyce robert, var2=12 14)
Further comments:
1) %seplist returns a macro quoted string (see the qscan function in the macro). You can either change the bottom of the macro from &emit to %unquote(&emit), or else unquote the results in your calling code.
(If you ever write a macro, view the mprint results in the log, go "I swear this code is correct", and start pulling your hair out, check your macro quoting ;-). To see what I mean, comment out or delete the line
%let filter = %unquote(&filter);
and resubmit with MPRINT active.
2) These lines could be combined:
%let var1=%upcase(&var1);
%let filter = upcase(name) in (%seplist(&var1,nest=qq));
%let filter = %unquote(&filter);
In fact, the %upcase function does implicit macro unquoting, so this would work, although it's perhaps less clear:
%let filter = upcase(name) in (%upcase(%seplist(&var1,nest=qq)));
3) The %superq function calls are probably unnecessary, but is defensive coding. Say you DID have the names AND and OR in your dataset (a contrived example I know):
data class;
set sashelp.class end=eof;
output;
if eof then do;
name="AND";output;
name="OR";output;
end;
run;
%sasphile(var1=alfred henry joyce robert and or)
The current macro works, even if your where clause contains reserved macro operators.
Now remove the %superq function calls, i.e.
%if (&var2 ne )
and resubmit.
Hope this helps...
Scott
Will try this and let you know.
I've been trying to find a better way to pass an IN() list in a data step than typing out the quote comma list.
Scott- I found the %seplist macro under another posting, and was caught in the hair-pulling stage as I have not encountered macro quoting before. Thanks for posting the unquote() solution.
Tom- Thanks for your %sysfunc solution.
I'm not sure which solution I will end up using, but I'm not going to have unbalanced quotes any more.
Thanks so much for your help!
Wendy T
I'm a big fan of Richard DeVenezia's %seplist macro (and pretty much all things DeVenezia...really smart dude).
The %seplist macro makes it really easy to move between data step and SQL syntax. Just keep your parameters as space delimited, and use %seplist when you need comma separated values.
See SAS Macros - by Richard A. DeVenezia and SAS Macros - by Richard A. DeVenezia - seplist.sas
I think the syntax would be (untested):
%macro test(var1=);
sql--statements;
where id in (%seplist(&var1,nest=QQ));
%mend;
%test(var1=A B C);
Download the macro, read the header, and test with %put %seplist(...).
HTH,
Scott
How about using contains operator instead of quoting them ?
where "&var1" contains strip(id) ;
or
where indexw("&var1",strip(id));
Ksharp
消息编辑者为:xia keshan
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.