DATA Step, Macro, Functions and more

Macro and where

Reply
Super Contributor
Posts: 648

Macro and where

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

Super User
Posts: 10,550

Re: Macro and where

;puI usually start with

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

and see if that works.

PROC Star
Posts: 1,237

Re: Macro and where

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.

Super User
Super User
Posts: 6,502

Re: Macro and where

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

Super Contributor
Posts: 648

Re: Macro and where

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.

Respected Advisor
Posts: 3,908

Re: Macro and where

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(Smiley Wink
    put (_all_) (=);
  run;
%mend test2;

%test2(var1='Alfred'  'Alice' )
%test2(var1='Alfred'  'Alice',var2='M' )
%test2(var2='M' )
%test2();

Super Contributor
Posts: 377

Re: Macro and where

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

Super Contributor
Posts: 648

Re: Macro and where

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

Super Contributor
Posts: 377

Re: Macro and where

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

Super Contributor
Posts: 648

Re: Macro and where

Will try this and let you know.

Frequent Contributor
Posts: 91

Re: Macro and where

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.  Smiley Happy

Thanks so much for your help!

Wendy T

Super Contributor
Posts: 377

Re: Macro and where

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

Super User
Posts: 9,691

Re: Macro and where

How about using contains operator instead of quoting them ?

where "&var1" contains strip(id) ;

or

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

Ksharp

消息编辑者为:xia keshan

Ask a Question
Discussion stats
  • 12 replies
  • 561 views
  • 0 likes
  • 8 in conversation