topic Re: SELECT statement inside a Macro in SAS Programming

SELECT statement inside a Macro

mvalsamis — Fri, 21 Jun 2024 12:47:31 GMT

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!

Re: SELECT statement inside a Macro

PaigeMiller — Fri, 21 Jun 2024 12:53:19 GMT

Inside the macro

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

Re: SELECT statement inside a Macro

Tom — Fri, 21 Jun 2024 12:59:01 GMT

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;

Re: SELECT statement inside a Macro

mvalsamis — Fri, 21 Jun 2024 14:09:44 GMT

Thanks PaigeMiller! It works!

Re: SELECT statement inside a Macro

mvalsamis — Fri, 21 Jun 2024 14:10:25 GMT

Thanks Tom!