BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
mvalsamis
Calcite | Level 5

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!

 

1 ACCEPTED SOLUTION

Accepted Solutions
PaigeMiller
Diamond | Level 26

Inside the macro

 

 select %sysfunc(translate(&vars,%str(,),%str( )))
--
Paige Miller

View solution in original post

4 REPLIES 4
PaigeMiller
Diamond | Level 26

Inside the macro

 

 select %sysfunc(translate(&vars,%str(,),%str( )))
--
Paige Miller
mvalsamis
Calcite | Level 5

Thanks PaigeMiller! It works!

Tom
Super User Tom
Super User

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
Calcite | Level 5

Thanks Tom!

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 284 views
  • 5 likes
  • 3 in conversation