BookmarkSubscribeRSS Feed
DJENS
Calcite | Level 5

Was hoping someone could help me.  Probably an easy answer to this, but I can't figure it out.

 

Let's say I have the following macro:

 

%macro first(dsn, bylist, outdat);

     proc sql;

         create table &outdat

         select &bylist

         from &dsn;

     quit;

%mend first;

%first(dsn=mydata, bylist=x y z, outdat=outfile);

 

My problem is that I want to build a select statement based on the variables I feed the macro using the macro variable bylist.  The problem is, of course, that the list has to be separated by commas in the select statement in proc sql.  I need to feed the macro a list of items to use in that select statement because the variables will change with each call to the macro and the number of variables in that bylist will change as well.  In other words I need to tell the macro what variables I want to use in the select statement with each call to the macro.  Is there an easy way to do this?  I'm drawing a blank.

 

Thanks in advance so much for your help.

       

 

1 REPLY 1
ChrisNZ
Tourmaline | Level 20

The comma is used as a separator for macro parameters.

You need to tell SAS that these commas are part of the parameter value and are not separators.

This is done thus:

 

%first(dsn=mydata, bylist=%str(x, y, z) , outdat=outfile);

 

Alternatively, if you don't want to burden the users of the macro, add the commas inside the macro (replace intermediate spaces with commas).

 

%first(dsn=mydata, bylist=x y z , outdat=outfile);

 

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 1 reply
  • 586 views
  • 0 likes
  • 2 in conversation