topic Re: IN operator - macro variable - characters in SAS Programming

IN operator - macro variable - characters

Alain38 — Wed, 20 Dec 2017 17:59:34 GMT

Dear all,

If I retrieve a list of ID codes in a macro variable as follows:

proc sql noprint;
	select distinct ID_code into : ID_list separated by ',' from Work.List;
quit;

and use it with a IN operator, inside a WHERE clause as follows:

data Work.Sample2;
	set Work.Sample (where=(ID_code in (&ID_list)));
run;

it only works if the ID_codes are numeric. When they are characters I get the following error "WHERE clause operator requires compatible variables" so I guess that some quotation marks are required but I don't know how to write it.

Thank you in advance for your help,

Re: IN operator - macro variable - characters

RW9 — Wed, 20 Dec 2017 18:01:22 GMT

For characters you need quotes around them:

proc sql noprint;
  select distinct ID_code into : ID_list separated by '","' from Work.List;
quit;

Note the double quotes.

Re: IN operator - macro variable - characters

Alain38 — Wed, 20 Dec 2017 18:18:55 GMT

Thank you very much for your help but it provides a syntax error: "NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended."

Re: IN operator - macro variable - characters

RW9 — Wed, 20 Dec 2017 18:20:07 GMT

Please post the code used and the log as I have no way of guessing what you ran.

Re: IN operator - macro variable - characters

novinosrin — Wed, 20 Dec 2017 18:28:21 GMT

data w;
input x $;
datalines;
1
2
3
4
5
;

proc sql noprint;
select distinct quote(trim(x)) into : ID_list separated by ',' from Work.w;
quit;
data w1;
input x $;
datalines;
2
3
4
;
data Work.Sample2;
set Work.w1 (where=(x in (&ID_list)));
run;

Re: IN operator - macro variable - characters

Astounding — Wed, 20 Dec 2017 18:34:32 GMT

The solution from @RW9 is viable, but the macro variable doesn't contain beginning and final quotes. You will need to change your subsequent code to match:

data Work.Sample2;
set Work.Sample (where=(ID_code in ("&ID_list")));
run;

Re: IN operator - macro variable - characters

Alain38 — Wed, 20 Dec 2017 18:39:43 GMT

@novinosrinYour solution is working, thank you so much!

Thank you @Astounding for this clarification, @RW9 code is working as well this way 🙂

Re: IN operator - macro variable - characters

ballardw — Wed, 20 Dec 2017 18:36:29 GMT

@Alain38 wrote:

Thank you very much for your help but it provides a syntax error: "NOTE 49-169: The meaning of an identifier after a quoted string might change in a future SAS release. Inserting white space between a quoted string and the succeeding identifier is recommended."

That is not an error, it is NOTE. There are a number of syntax elements that have special meanings when the value is quoted and followed immediately by a character: '01JAN2017'd (the 'd indicates the quoted element should be treated as a date), "07:01:23"t (the quoted element should be treated as a time, "stupid varname"n (the quoted value is a variable name which may not follow typical SAS variable name rules), ' 'dt would be treated as a datetime. So when you use any thing that is quoted followed by a character without a space you get the resulting NOTE as a warning that in the future the code may behave differently.

Re: IN operator - macro variable - characters

Tom — Wed, 20 Dec 2017 18:41:21 GMT

For character strings you need to quote the values so that they look like string literals. You can use the QUOTE() function and avoid problems that would be caused by embedded quote characters in the values.

select distinct quote(trim(ID_code)) into :ID_list separated by ',' from Work.List;

If your values might contain macro triggers (& or %) then using single quote for the quoting character instead of double quote will prevent the macro processor from trying to evaluate them when you expand the macro value. Also note that SAS will happily allow space delimited lists instead of comma delimited lists in the IN () operator. This will make your macro variable easier to work with in other ways such as passing it into macro calls.

select distinct quote(trim(ID_code),"'") into :ID_list separated by ' ' from Work.List;

Re: IN operator - macro variable - characters

Alain38 — Wed, 20 Dec 2017 18:44:01 GMT

Thank you all for answering me so quickly and for your explanations

Re: IN operator - macro variable - characters

Ksharp — Thu, 21 Dec 2017 13:20:48 GMT

Or use SYMGET() and you don't need care about if it is numeric or character.

proc sql noprint;
select distinct age into : list separated by ',' from sashelp.class;
quit;

data want;
 set sashelp.class;
 if findw(symget('list'),strip(age),',');
run;







proc sql noprint;
select distinct name into : list separated by ',' from sashelp.class;
quit;

data want;
 set sashelp.class;
 if findw(symget('list'),strip(name),',');
run;