SELECT statement inside a Macro

[mvalsamis]

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!

[PaigeMiller · Accepted solution]

Inside the macro

 select %sysfunc(translate(&vars,%str(,),%str( )))

[mvalsamis]

Thanks PaigeMiller! It works!

[Tom]

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;

 

[mvalsamis]

Thanks Tom!