topic Re: Macro and where in SAS Programming

Macro and where

SASPhile — Fri, 15 Feb 2013 20:54:25 GMT

%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?

Re: Macro and where

ballardw — Fri, 15 Feb 2013 23:28:37 GMT

;puI usually start with

%test (var1=%str('a' 'b'));

and see if that works.

Re: Macro and where

Quentin — Sat, 16 Feb 2013 02:40:49 GMT

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.

Re: Macro and where

Tom — Sat, 16 Feb 2013 04:10:55 GMT

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);

Re: Macro and where

SASPhile — Sun, 17 Feb 2013 02:16:32 GMT

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.

Re: Macro and where

Patrick — Sun, 17 Feb 2013 02:45:24 GMT

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();

Re: Macro and where

ScottBass — Sun, 17 Feb 2013 08:43:22 GMT

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

Re: Macro and where

ScottBass — Sun, 17 Feb 2013 10:24:42 GMT

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

Re: Macro and where

SASPhile — Tue, 19 Feb 2013 14:38:44 GMT

%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.

Re: Macro and where

ScottBass — Wed, 20 Feb 2013 00:09:41 GMT

OK, you say:

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. (emphasis added, what's with the ellipsis?)

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

Re: Macro and where

SASPhile — Wed, 20 Feb 2013 21:12:06 GMT

Will try this and let you know.

Re: Macro and where

WendyT — Mon, 22 Jul 2013 15:53:05 GMT

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

Re: Macro and where

Ksharp — Tue, 23 Jul 2013 01:35:34 GMT

How about using contains operator instead of quoting them ?

where "&var1" contains strip(id) ;

where indexw("&var1",strip(id));

Ksharp

消息编辑者为：xia keshan