BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Alain38
Quartz | Level 8

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,

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20


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

10 REPLIES 10
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.   

Alain38
Quartz | Level 8

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Astounding
PROC Star

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;

 

ballardw
Super User

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

novinosrin
Tourmaline | Level 20


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;

 

Alain38
Quartz | Level 8

@novinosrinYour solution is working, thank you so much!

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

Tom
Super User Tom
Super User

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;

 

Alain38
Quartz | Level 8

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

Ksharp
Super User

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;

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
  • 10 replies
  • 3486 views
  • 6 likes
  • 7 in conversation