DATA Step, Macro, Functions and more

SAS Macro Question

Reply
Occasional Contributor
Posts: 7

SAS Macro Question

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.

       

 

PROC Star
Posts: 2,316

Re: SAS Macro Question

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( ) ) )

Ask a Question
Discussion stats
  • 1 reply
  • 92 views
  • 0 likes
  • 2 in conversation