CODEWITHSUNDEEP
sqlsassas-macro
alex
alex

Reputation: 844

Create a single quoted list macro variable from a data column in sas

If I have a data set with a column like this:

ID
A1028
A30900
B7018

I am trying to convert this to a list with the values in single quotes, separated by commas like this:

&ID= 'A1028','A30900','B7018'

I can make a comma separated list by:

proc sql;
select distinct ID
into :ID separated by ","
from data;
quit; 

to get

&ID= A1028,A30900,B7018

But I can't find how to add single quotes. I have tried several solutions, but they have double quotations. Ultimately I am trying to use this macro variable to filter a larger data set:

proc sql;
select * 
from data2
where ID in (&ID.)
quit;

But I think it will only work with single quotes because ID is a character value. It did not work with unquoted IDs or double quoted IDs. Any help is greatly appreciated.

Upvotes: 0

Views: 346

Answers (2)

Tom
Tom

Reputation: 51566

Add the quotes in the SAS code and avoid having to worry about it in macro code. You can use the QUOTE() function.

select distinct quote(trim(ID),"'")
  into :ID separated by ','
  from data
;

The TRIM() function will make so the strings generated will not including the meaningless trailing spaces into the macro variables. Do not use the StRIP() function as that will also remove leading spaces, which are meaningful.

And if you are only going to use the list to generate SAS code (ie not as part of explicitly pass-thru SQL query) then use a space instead of a comma as the separator. The IN operator in SAS will allow you to use either a comma or a space (or some combination of both). And a macro variable without commas is a lot easier to use for other things in SAS macro code.

Upvotes: 2

Stu Sztukowski
Stu Sztukowski

Reputation: 12849

Use the cats function to add single quotes. In SAS, in() accepts both single and double quotes so the only reason why it wouldn't work is if the data doesn't exist. Just to help out, I want to provide you with how to create a single-quoted macro list in SQL just in case you need it in the future.

proc sql;
    select distinct cats("'", ID, "'")
    into :ID separated by ","
    from data;
quit;

However, if you're going to be filtering one dataset with another, why not do an inner join?

proc sql;
    select t2.* 
    from data as t1, data2 as t2
    where t1.id = t2.id;
quit;

Or, alternatively:

proc sql;
    select * 
    from data2
    where id in(select id from data);
quit;

Upvotes: 0

Related Questions