DATA Step, Macro, Functions and more

IN operator - macro variable - characters

Accepted Solution Solved
Reply
Contributor
Posts: 26
Accepted Solution

IN operator - macro variable - characters

[ Edited ]

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,


Accepted Solutions
Solution
‎12-20-2017 01:33 PM
PROC Star
Posts: 1,605

Re: IN operator - macro variable - characters

[ Edited ]


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;

 

View solution in original post


All Replies
Super User
Super User
Posts: 9,441

Re: IN operator - macro variable - characters

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.   

Contributor
Posts: 26

Re: IN operator - macro variable - characters

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

Super User
Super User
Posts: 9,441

Re: IN operator - macro variable - characters

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

Super User
Posts: 6,644

Re: IN operator - macro variable - characters

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;

 

Super User
Posts: 13,358

Re: IN operator - macro variable - characters


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.

Solution
‎12-20-2017 01:33 PM
PROC Star
Posts: 1,605

Re: IN operator - macro variable - characters

[ Edited ]


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;

 

Contributor
Posts: 26

Re: IN operator - macro variable - characters

[ Edited ]
Posted in reply to novinosrin

@novinosrinYour solution is working, thank you so much!

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

Super User
Super User
Posts: 7,944

Re: IN operator - macro variable - characters

[ Edited ]

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;

 

Contributor
Posts: 26

Re: IN operator - macro variable - characters

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

Super User
Posts: 10,699

Re: IN operator - macro variable - characters

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 275 views
  • 6 likes
  • 7 in conversation