topic Re: How to create a var list in a macro? in SAS Programming

How to create a var list in a macro?

Moksha — Wed, 07 Jun 2023 12:15:48 GMT

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(&amp;vars));

%let varlist=;
%do i = 1 %to &amp;varcount;
       %let varname = %qscan(&amp;vars,&amp;i);
       %let var&amp;i = &amp;varname;
       %let varlist = %unquote(%sysfunc(catx(',', &amp;varlist, &amp;&amp;var&amp;i)));
        %put var list is &amp;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

Thanks

Re: How to create a var list in a macro?

PaigeMiller — Wed, 07 Jun 2023 12:31:41 GMT

%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.

Re: How to create a var list in a macro?

Moksha — Wed, 07 Jun 2023 12:45:05 GMT

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'

Re: How to create a var list in a macro?

PaigeMiller — Wed, 07 Jun 2023 12:47:32 GMT

See this: https://communities.sas.com/t5/SAS-Programming/Macro-Variable-Quoting/m-p/877543#M346659

and also this: https://communities.sas.com/t5/SAS-Programming/Macro-Variable-Quoting/m-p/877567#M346675

Re: How to create a var list in a macro?

Tom — Wed, 07 Jun 2023 12:58:29 GMT

@Moksha wrote:
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;

Example usage:

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.

&sep.%sysfunc(quote(&word,%str(%')))

There is a more robust version of %QLIST() available on-line.

Re: How to create a var list in a macro?

Kurt_Bremser — Wed, 07 Jun 2023 13:10:07 GMT

data _null_;
call symputx('varlist',translate(compress("&vars.","'")," ",","));
run;

No macro and no looping needed.

Re: How to create a var list in a macro?

Quentin — Wed, 07 Jun 2023 13:08:58 GMT

I'm a fan of Richard Devenezia's %seplist: https://www.devenezia.com/downloads/sas/macros/index.php?m=seplist

Re: How to create a var list in a macro?

Moksha — Wed, 07 Jun 2023 13:24:55 GMT

Thank you very much. That helped me a lot and it's working for my requirement.

Re: How to create a var list in a macro?

Moksha — Wed, 07 Jun 2023 13:58:22 GMT

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

&sep."&word"

why it is without semicolon ; and why it is before

 %let sep=,;

Can you please clarify?

Re: How to create a var list in a macro?

Moksha — Wed, 07 Jun 2023 17:51:38 GMT

Thank you very much for providing such a valuable information. I will use this wherever applicable.