Hi all in the Community! I want to create a macro with a SQL statement in it. The variables must be one of the macro's parameters. The macro is as follows:
%macro sqlstep(vars=);
proc sql;
select <insert variables here>
from work.class;
quit;
%mend;
Suppose I want to select 2 variables: name and gender. A way to select them is to write the macro as follows:
%macro sqlstep(vars=);
proc sql;
select &vars
from work.class;
quit;
%mend;
And I select them when invoking the macro:
%sqlstep(vars=name %str(,) gender).
But this is a time consuming way, and probably inefficient (imagine I have 50 variables instead of 2). Do you know any way to put the comma inside the macro, instead of the invocation? I want to invoke it like this:
%sqlstep(vars=name gender)
That's all. I hope that I didn't write a novel!
Inside the macro
select %sysfunc(translate(&vars,%str(,),%str( )))
Inside the macro
select %sysfunc(translate(&vars,%str(,),%str( )))
Thanks PaigeMiller! It works!
For your simple example just use SAS code instead of SQL code and then you won't have to worry so much about commas. Here are a couple of examples:
proc print data=class;
var &vars;
run;
proc sql;
select * from class(keep=&vars);
quit;
But in general you just need a way to convert the list from space delimited to comma delimited. There are many examples to do this that have been shared on this forum over the years. One example is to use TRANSLATE() function. To make it easier for the user also use the COMPBL() function to first reduce multiple spaces into one.
%macro sqlstep(vars=);
proc sql;
select %sysfunc(translate(%qsysfunc(compbl(&vars)),%str(,),%str( )))
from work.class;
quit;
%mend;
Thanks Tom!
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.