@Reeza @PaigeMiller In the qlist macro, I don't want to create other macro varible, my_list. Can we tackle this without creating new macro varaible? Only varlist (per my post, it is 'Parameter') macro variable to be used in the code below.
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas'; %include qlist; %let varlist = Data Management,Operations,Sales; %let my_list= %qlist(%quote(&varlist.), paren=0, comma=1, delimit=',', dsd = 0, quote=2); %put &my_list.;
@Babloo wrote:
@Reeza @PaigeMiller In the qlist macro, I don't want to create other macro varible, my_list. Can we tackle this without creating new macro varaible? Only varlist (per my post, it is 'Parameter') macro variable to be used in the code below.
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas'; %include qlist; %let varlist = Data Management,Operations,Sales; %let my_list= %qlist(%quote(&varlist.), paren=0, comma=1, delimit=',', dsd = 0, quote=2); %put &my_list.;
Yes, you don't need to use my_list, you can use varlist on the left of the equal sign
@PaigeMiller Like this,
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas'; %include qlist; %let varlist = Data Management,Operations,Sales; %let varlist = %qlist(%quote(&varlist.), paren=0, comma=1, delimit=',', dsd = 0, quote=2); %put &varlist.;
@Babloo wrote:
@PaigeMiller Like this,
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas'; %include qlist; %let varlist = Data Management,Operations,Sales; %let varlist = %qlist(%quote(&varlist.), paren=0, comma=1, delimit=',', dsd = 0, quote=2); %put &varlist.;
Since you're dealing with values passed in from elsewhere, you might want to change to using %superq() instead of %quote() to mask any macro triggers, and also change to passing quote=1 to get single quotes around each item of the list. Also note that you don't have to assign the new quoted list to a macro variable at all. You can just call %qlist where you want to generate the quoted list, e.g.:
options mprint ;
data _null_ ;
call symputx('varlist','Data Management,Operations,Sales,Franks&Beans') ;
run ;
%put varlist=%superq(varlist) ;
proc print data=have noobs;
where Division in ( %qlist(%superq(varlist), paren=0, comma=1, delimit=',', dsd = 0, quote=1) ) ;
run;
@PaigeMiller just calling %qlist macro is throwing an error.
90 ! %qlist(%superq(varlist), paren=0, comma=1, delimit=',', dsd = 0, quote=1) ) ; - 22 WARNING: Apparent invocation of macro QLIST not resolved. 90 %qlist(%superq(varlist), paren=0, comma=1, delimit=',', dsd = 0, quote=1) ) ; - 76 ERROR: Syntax error while parsing WHERE clause. ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, -. ERROR 76-322: Syntax error, statement will be ignored.
@Babloo wrote:
@PaigeMiller just calling %qlist macro is throwing an error.
90 ! %qlist(%superq(varlist), paren=0, comma=1, delimit=',', dsd = 0, quote=1) ) ; - 22 WARNING: Apparent invocation of macro QLIST not resolved. 90 %qlist(%superq(varlist), paren=0, comma=1, delimit=',', dsd = 0, quote=1) ) ; - 76 ERROR: Syntax error while parsing WHERE clause. ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value, -. ERROR 76-322: Syntax error, statement will be ignored.
You still need to run the %include to compile the macro, before calling it:
filename qlist url 'https://raw.githubusercontent.com/sasutils/macros/master/qlist.sas';
%include qlist;
If you don't want to run that code in every session when you use the macro, then you would need to download qlist.sas, and put qlist.sas in your autocall library search path. Then when you call %qlist() the first time in a SAS session, SAS will search the autocall directories for qlist.sas and it will compile the macro.
I typically do the following to quote separated values within a macro variable
%let parameter=Data Management,Operations,Sales;
%put &=parameter;
%let parameter=%str(%')%qsysfunc(tranwrd(¶meter,%str(,), %str(%',%')))%str(%');
%put &=parameter;
/* To use this macro variable in a where clause, I would use %unquote() */
Where Division in (%unquote(¶meter));
Hope this helps
@AhmedAl_Attar I'm getting an error
33 %put &=parameter; PARAMETER=Sales,Operations 34 %let parameter_2=%str(%')%qsysfunc(tranwrd(¶meter,%str(,), %str(%',%')))%str(%'); SYMBOLGEN: Macro variable PARAMETER resolves to Sales,Operations ERROR: The function TRANWRD referenced by the %SYSFUNC or %QSYSFUNC macro function has too many arguments. 35 %put &=parameter; SYMBOLGEN: Macro variable PARAMETER resolves to '' SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. PARAMETER='' 36
Hello @Babloo
Another reason why I recommend using the %qlist macro is that someone has already done the very hard work of creating macro code that does the task YOU want. To me, it is a very smart move to use the hard work of others (who have made this code available to everyone) to get your job done quickly and correctly.
Change it to
%let parameter_2=%str(%')%qsysfunc(tranwrd(%superq(parameter),%str(,),%str(%',%')))%str(%');
%put &=parameter_2;
/*Here are two ways:*/
data have;
set sashelp.class;
run;
/*First one :*/
%let parameter=Alfred,Mary,Ronald ;
%let want="%sysfunc(tranwrd(%bquote(¶meter.),%str(,),%str(",")))" ;
%put &=want.;
proc print data=have noobs;
where name in ( &want. ) ;
run;
/*Second one */
%let parameter=Alfred,Mary,Ronald ;
proc print data=have noobs;
where findw("¶meter.",strip(name),',') ;
run;
@Ksharp In first solution, can we do it without creating the new macro variable (e.g. want)? In second solution, how to handle it if we don't know which field from the SAS dataset that macro variable (e.g. name) values from?
" first solution, can we do it without creating the new macro variable "
Just put it at the position of &want.
/*First one :*/ %let parameter=Alfred,Mary,Ronald ; proc print data=have noobs; where name in ( "%sysfunc(tranwrd(%bquote(¶meter.),%str(,),%str(",")))" ) ; run;
"how to handle it if we don't know which field from the SAS dataset "
I don't understand, since "name" is already appeared in your PROC PRINT .
@Babloo If your source string doesn't contain multibyte characters or single quotes then below should work.
%let parameter=Data Management,Operations,Sales ;
/* construct value for macro variable parameter that includes blanks, a tab and a line feed */
data _null_;
call symput('parameter',cat('Data','0A'x,' Management , ','09'x,' Operations , Sales '));
run;
%put parameter: %nrbquote(¶meter);
data _null_;
length string $1000;
/* replace all whitespace characters with a blank */
string=prxchange('s/\s+/ /',-1,strip("¶meter"));
/* remove all blanks before or after a comma and add quotes before and after a comma */
string=prxchange("s/ *(,) */','/",-1,strip(string));
/* create new macro varialbe, add quotes at the start and end of the string */
call symputx('parameter_new',cats("'",string,"'"));
run;
%put parameter_new: %nrbquote(¶meter_new);
parameter_new: 'Data Management','Operations','Sales'
@Patrick two questions a) How to tweak your solution without creating the new macro variable, 'parameter_new'? b) In the below code block, you've hardcoded the values but in real life the values of macro variable will change and it can have any special/invisible characters.
%let parameter=Data Management,Operations,Sales ; /* construct value for macro variable parameter that includes blanks, a tab and a line feed */ data _null_; call symput('parameter',cat('Data','0A'x,' Management , ','09'x,' Operations , Sales ')); run;
On a separate note, I'd like to know how to handle if we have multibyte characters or single quotes in the macro variable values.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: