sqlsassas-macro

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.

Solution

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