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.
%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;
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')
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;
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?
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.
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 ;
%let GROUP= SUV Sedan;
data want;
set sashelp.cars;
if findw(symget('group'), strip(type) ) ;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.