topic Can we use Macro Variable with single quotes to use it in "IN" operator in SAS Programming

Can we use Macro Variable with single quotes to use it in "IN" operator

SASuserlot — Thu, 01 Dec 2022 01:35:16 GMT

I have the following scenario. I am want to use multiple 'sex ' criteria to subset the data. Is it possible? what's the best way to achieve it to control sub setting conditions. In the below example I want flexibility where I can enter one or two observation for &sex macro variable. Thanks

 %macro filter(sex= );
  data class;
  set sashelp.class;
  if upcase(sex) in ( "&sex");
  *if upcase (sex) in ('F' 'M'); *how to achieve this using macro variable:
  run;
  %mend;
 %filter( sex= F );
 %*filter ( sex = %nrbquote('F' 'M'));*I wanna use like this or any other alternatives to call macro;

Re: Can we use Macro Variable with single quotes to use it in "IN" operator

Astounding — Thu, 01 Dec 2022 01:56:33 GMT

A few possible combinations will do this, but here is perhaps the simplest.

First, get rid of the double quotes:

if upcase(sex) in ( &sex );

Then call the macro supplying the characters that should replace &sex:

%filter (sex = 'F' 'M')

Re: Can we use Macro Variable with single quotes to use it in "IN" operator

Ksharp — Thu, 01 Dec 2022 08:35:48 GMT

Try FINDW() function:

%macro filter(sex= );
  data class;
  set sashelp.class;
  if  findw("&sex",strip(upcase(sex)));
  run;
  %mend;
 %filter ( sex =F  M )

Re: Can we use Macro Variable with single quotes to use it in "IN" operator

PaigeMiller — Thu, 01 Dec 2022 13:38:55 GMT

@SASuserlot wrote:

I have the following scenario. I am want to use multiple 'sex ' criteria to subset the data. Is it possible? what's the best way to achieve it to control sub setting conditions. In the below example I want flexibility where I can enter one or two observation for &sex macro variable. Thanks
 %macro filter(sex= );
  data class;
  set sashelp.class;
  if upcase(sex) in ( "&sex");
  *if upcase (sex) in ('F' 'M'); *how to achieve this using macro variable:
  run;
  %mend;
 %filter( sex= F );
 %*filter ( sex = %nrbquote('F' 'M'));*I wanna use like this or any other alternatives to call macro;

Remember that when you execute code that contains macros and macro variables, the values of the macro variables are used in place of the actual macro variable to generate code. Your macro above would, when executed, produce this code:

if upcase(sex) in ( "'F' 'M'");

this will not match the sex values in SASHELP.CLASS which are 'F' or 'M', it will match a record if the value of sex is "'F' 'M'", but there is no such record that has this value for sex. So you really need to "envision" what the code generated by the macro will be. Hence, when @Astounding tells you that you should remove the double quotes around &sex and use %filter (sex = 'F' 'M'), then this will produce the line of code

if upcase(sex) in ( 'F' 'M');

which is what you really want.

In addition, you could turn on macro debugging options

options mprint;

before you run your macro, and then you can see in the log that the code generated is not what you want it to be:

MPRINT(FILTER):    data class;
MPRINT(FILTER):   set sashelp.class;
MPRINT(FILTER):   if upcase(sex) in ( "'F' 'M'");
MPRINT(FILTER):   run;

Re: Can we use Macro Variable with single quotes to use it in "IN" operator

Quentin — Thu, 01 Dec 2022 13:52:57 GMT

Since you mentioned flexibility, an alternate design would be to allow the user to pass any subsetting expression, not just values to allow subsetting by gender. Sometimes I do stuff like:

%macro filter(where=1 );
  data class;
    set sashelp.class;
    where &where;
  run;
  proc print data=class ;
  run ;
%mend;

%filter()
%filter(where=(sex='F') )
%filter(where=(sex IN ('F','M')) )  /*this comma is not a problem because the parentheses mask it*/
%filter(where=(age<15))
%filter(where=(Name =: 'J' and age<=13))

For a utility macro that is designed to be used by a SAS programmer, I find the flexibility of allowing them to provide any valid WHERE clause is often valuable.

Re: Can we use Macro Variable with single quotes to use it in "IN" operator

SASuserlot — Thu, 01 Dec 2022 19:22:24 GMT

Thank you @Quentin providing me the flexible options.

Thank you every one who provided the different ways to achieve the solutions. I really appreciate taking your time to answer this and providing insights how to use the code more useful way.

Thank you @PaigeMiller @Astounding @Ksharp @Quentin .