Hi All, I have a macro which takes a list of variables say var1, var2, var3, var4, var5 and var6 as a parameter as shown the code below . In need to create a new variable say varlist containing these input variables separated by commas, without any quotation marks. I have tried it as follows, but it is not working :
%macro(vars= var1 var2 var3 var4 var5 var6); %let varcount = %sysfunc(countw(&vars)); %let varlist=; %do i = 1 %to &varcount; %let varname = %qscan(&vars,&i); %let var&i = &varname; %let varlist = %unquote(%sysfunc(catx(',', &varlist, &&var&i))); %put var list is &varlist; %mend; In the following NOTE is there in the log: NOTE: Line generated by the macro variable "VARLIST" 1 var1','var2','var3','var4','var5','var6
Please, advise how achieve the result as var1,var2,var3,var4,var5,var6
%let varlist=var1 var2 var3 var4 var5 var6; %let varlist_wanted=%sysfunc(translate(&varlist,%str(,),%str( ))); %put &=varlist_wanted;
However, I should point out that if you don't want spaces and you do want commas, you probably should NOT create the macro variable with spaces in the first place; create it with commas and then you don't need the above code.
Thank you very much. It's working. Can you please help me how to get the out as varlist_wanted = 'var1', 'var2', 'var3', 'var4', 'var5', 'var6'
Do you actually need single quotes (are you using the macro to generate SQL code in some external database) or are double quotes ok (are you using the macro to generate SAS code)?
%macro qlist(vars); %local i sep word; %do i = 1 %to %sysfunc(countw(&vars,%str( ))); %let word=%scan(&vars,&i,%str( )); &sep."&word" %let sep=,; %end; %mend;
21 %let varlist=%qlist(vars= var1 var2 var3 var4 var5 var6); 22 %put &=varlist; VARLIST='var1' ,'var2' ,'var3' ,'var4' ,'var5' ,'var6'
If you need the single quotes you could use the QUOTE() function to add them.
There is a more robust version of %QLIST() available on-line.
Thank you very much Tom. This is working and I will use this as the code is small. As mentioned by PaigeMiller and you, the Qlist() macro available at github is more sophisticated and it's also working for my requirement. I am really learning new concepts and different ways to solve a problem, in this forum. It's really very helpful. I thank each and everyone who responded.
I didn't understand
why it is without semicolon ; and why it is before
Can you please clarify?
data _null_; call symputx('varlist',translate(compress("&vars.","'")," ",",")); run;
No macro and no looping needed.
This kind of need comes up a lot in the macro language. I have a list of <variables|values|dataset names> and I need to <add|remove|change> a <delimiter|quotes|brackets>. You might want to look into using a utility macro for this sort of manipulation.
I'm a fan of Richard Devenezia's %seplist: https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist
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.