BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8

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

12 REPLIES 12
ballardw
Super User

;puI usually start with

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

and see if that works.

Quentin
Super User

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.

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Tom
Super User Tom
Super User

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

SASPhile
Quartz | Level 8

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.

Patrick
Opal | Level 21

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

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
SASPhile
Quartz | Level 8

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

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
SASPhile
Quartz | Level 8

Will try this and let you know.

WendyT
Pyrite | Level 9

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

ScottBass
Rhodochrosite | Level 12

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


Please post your question as a self-contained data step in the form of "have" (source) and "want" (desired results).
I won't contribute to your post if I can't cut-and-paste your syntactically correct code into SAS.
Ksharp
Super User

How about using contains operator instead of quoting them ?

where "&var1" contains strip(id) ;

or

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

Ksharp

消息编辑者为:xia keshan

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 1656 views
  • 0 likes
  • 8 in conversation