topic Re: How to get each word in macro variable wrapped by quotation marks(" ")? in SAS Programming

How to get each word in macro variable wrapped by quotation marks(" ")?

braam — Sat, 11 Jan 2020 16:13:12 GMT

Can I change GROUP to GROUP2 systematically?

%let GROUP= SUV Sedan;
%let GROUP2= "SUV" "Sedan";

I tried to do this myself using scan function, etc. but didn't manage to do so.

It's a simplified example of the situation I am facing. My GROUP variable includes like 100 words and I want to generate GROUP2 macro variable so that I can use it in WHERE statement of data step.

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

novinosrin — Sat, 11 Jan 2020 16:41:47 GMT

%let GROUP= SUV Sedan;

%macro t;
 %global group2;
 %do i=1 %to %sysfunc(countw(&group,%str( )));
 %let Group2=&group2 "%scan(&group,&i,%str( ))";
 %end;
%mend t;

%t

%put &=group2;

LOG:

95   %let GROUP= SUV Sedan;
96
97   %macro t;
98    %global group2;
99    %do i=1 %to %sysfunc(countw(&group,%str( )));
100   %let Group2=&group2 "%scan(&group,&i,%str( ))";
101   %end;
102  %mend t;
103
104  %t
105
106  %put &=group2;
GROUP2="SUV" "Sedan"

And as a matter of fact, you could use QUOTE function as an alternative to explicit " " quotes like


%macro t;
 %global group2;
 %do i=1 %to %sysfunc(countw(&group,%str( )));
 %let Group2=&group2 %sysfunc(quote(%scan(&group,&i,%str( ))));
 %end;
%mend t;

%t
%put &=group2;

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

Tom — Sat, 11 Jan 2020 17:38:32 GMT

If your list of words is using space as the delimiter then you can do it in one statement using the TRANWRD() function to convert the spaces into quoted spaces.

%let group2="%sysfunc(tranwrd(&group,%str( )," "))";

Example:

263   %let GROUP= SUV Sedan;
264   %let GROUP2= "%sysfunc(tranwrd(&group,%str( )," "))";
265   %put &=group2;
GROUP2="SUV" "Sedan"

Note that the list needs to have one and only one space between the words for this method to work. You can insure that property by using the COMPBL() function.

Example:

267   %let GROUP= SUV    Sedan;
268   %let GROUP2= "%sysfunc(tranwrd(%sysfunc(compbl(&group)),%str( )," "))";
269   %put &=group2;
GROUP2="SUV" "Sedan"

Frequently you will want to use single quotes instead of double quotes. For example when the words contain macro triggers, % or &, or when you want to use the resulting string in pass thru SQL to a system that only allows the use of single quotes in character literals.

273   %let GROUP= SUV    Sedan;
274   %let GROUP2= %unquote(%str(%')%qsysfunc(tranwrd(%sysfunc(compbl(&group)),%str( ),' '))%str(%'));
275   %put &=group2;
GROUP2='SUV' 'Sedan'

Note that it gets even more complicated if you need to worry about words that contain the quote character itself.

Fortunately there are many macros out there that implement some form of adding quotes to lists in macro variables. Here is a link to one adopted from the original %QLIST() macro created by Tom Hoffman 25 years ago.

https://github.com/sasutils/macros/blob/master/qlist.sas

Examples:

360   %let GROUP=SUV  Sedan;
361   %put WHERE type in %qlist(&group);
WHERE type in ('SUV','Sedan')
362   %put WHERE type in %qlist(&group,dsd=1);
WHERE type in ('SUV','','Sedan')
363
364   %let group=SUV||Sedan;
365   %put WHERE type in %qlist(&group,delimit=|);
WHERE type in ('SUV','','Sedan')
366   %put WHERE type in %qlist(&group,delimit=|,dsd=0);
WHERE type in ('SUV','Sedan')

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

PaigeMiller — Sat, 11 Jan 2020 17:23:32 GMT

If somehow you are extracting these values from a data set (SUV and SEDAN are values in a data set) then you can use PROC SQL.

proc sql noprint;
     select distinct quote(variablename) into :group separated by ' ' from datasetname;
quit;

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

novinosrin — Sat, 11 Jan 2020 17:36:41 GMT

Sir @PaigeMiller Very neat and perhaps the best idea to do things in a cleanest way possible.

Just a thought, once the source is a dataset, which makes it easiser to edit/modify with potential to hold numerous values, why bother to even have a macro variable?

Why not a use the Dataset as a LOOK-UP dataset?

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

PaigeMiller — Sun, 12 Jan 2020 00:13:22 GMT

You could do this with a data set as well in many situations. I suppose the advantage of PROC SQL is that you can use DISTINCT if needed, whereas that's not that easy using data sets.

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

Ksharp — Sun, 12 Jan 2020 11:09:22 GMT

If you want use it in WHERE statement of data step. you could try SYMGET().

if findw(symget('group'), strip(group) ) then ....

%let GROUP= SUV Sedan;

%let group2="%sysfunc(prxchange(s/\s+/" "/,-1,&group))" ;

%put group=&group ;
%put group2=&group2 ;

Re: How to get each word in macro variable wrapped by quotation marks(" ")?

Ksharp — Sun, 12 Jan 2020 11:12:29 GMT

%let GROUP= SUV Sedan;

data want;
 set sashelp.cars;
 if findw(symget('group'), strip(type) ) ;
run;