topic Re: Using a Character macro variable list in SAS Programming

Using a Character macro variable list

frupaul — Tue, 24 Jul 2018 15:40:05 GMT

Hi Everyone,

I have just run into a problem with macro variable lists.

I have 2 data sets:

created with the following codes respectively:

data Subidiaries;
length subs $25;
input Subs $;
cards;
Walmart
Sainsbury
Argos
Holland
PureGym
;

data stats;
length subs $25 Customer_count 8.;
input subs $ customer_count;
cards;
Walmart 33
Argos 55
Pimps 66
Lugos 67
;

I create a macro variable list from the first table above use in filtering on the second table. The queries used are:

proc sql noprint;
select subs
into :List_subs separated by ' '
from Subidiaries;
quit;

data subset;
set stats;
where subs in (&List_subs);
quit;

However, I get the below error in the log. Can anyone advice me on how to resolve this?

Re: Using a Character macro variable list

novinosrin — Tue, 24 Jul 2018 15:44:22 GMT

correction:

proc sql noprint;
select quote(trim(subs))
into :List_subs separated by ' '
from Subidiaries;
quit;

data subset;
set stats;
where subs in (&List_subs);
run;

Re: Using a Character macro variable list

ballardw — Thu, 05 Mar 2020 17:37:02 GMT

The data step IN wants to see quotes around each individual value

in ('Walmart' 'Sainsbury' )

Change

proc sql noprint;
select subs
into :List_subs separated by ' '
from Subidiaries;
quit;

proc sql noprint;
select quote( strip(subs))
into :List_subs separated by ' '
from Subidiaries;
quit;

to place quotes around each value. The strip may be needed to preven having spaces depending on the actual values and declared length of the variable SUBS.

If you are using that list elsewhere where you do not want the quotes you'll need to build to separate list variables.

Re: Using a Character macro variable list

Tom — Tue, 24 Jul 2018 16:28:09 GMT

So you need quotes around string literals. So either add the quotes when making the macro variable. You can use the QUOTE() function in your SQL query.

It is a common enough problem to convert a space delimited list of words into a list of quoted words that there are many macros available to make the conversion. For example: %qlist

But for simple list with just a single space between the words you can do it with a simple call to TRANWRD() function.

data subset;
  set stats;
  where subs in ("%sysfunc(tranwrd(&List_subs,%str( )," "))");
run;

You can clean up a user entered list that might have multiple spaces between the words by using the COMPBL() function.

%let qlist=%sysfunc(compbl(&list_subs));
%let qlist="%sysfunc(tranwrd(&qlist,%str( )," "))";