topic Passing an arbitrary number of parameters to a macro in SAS Programming

Passing an arbitrary number of parameters to a macro

Forgotthesemicolon — Tue, 24 Jan 2012 23:08:52 GMT

Hi everyone, I'm new here so I'm not sure if I'm putting this thread in the right place but here goes :smileysilly:

I'm writing a macro that assembles a group of datasets into a single dataset in a certain layout for export. Now this macro needs to be able to, in general, accept any number of datasets as parameters, ie:

%MACRO Unite_Tables(table1,table2,...,tableN);

%MEND;

However the only way I know to declare parameters in a macro definition is one by one. Is there some way to pass an arbitrary number of parameters to a macro? At the moment I'm putting the names of the tables into a dataset and passing the name of that dataset to the macro and then reading those names into macros, but it's kind of an ugly solution.

Re: Passing an arbitrary number of parameters to a macro

data_null__ — Tue, 24 Jan 2012 23:35:35 GMT

PARMBUFF
PBUFF 
assigns the entire list of parameter values in a macro call, including the parentheses in a name-style invocation, as the value of the automatic macro variable SYSPBUFF.
Using the PARMBUFF option, you can define a macro that accepts a varying number of parameter values.
If the macro definition includes both a set of parameters and the PARMBUFF option, the macro invocation causes the parameters to receive values and also causes the 
entire invocation list of values to be assigned to SYSPBUFF.
To invoke a macro defined with the PARMBUFF option in a windowing environment or interactive line mode session without supplying a value list,
 enter an empty set of parentheses or more program statements after the invocation to indicate the absence of a value list, even if the macro definition contains no parameters

But I would just use use a KEYWORD parameter like DATA= <blank delimited list of data set names>

Re: Passing an arbitrary number of parameters to a macro

Linlin — Tue, 24 Jan 2012 23:51:19 GMT

Is this helpful?

data aa bb cc dd ee ff;

do i=1 to 5;

output;

end;

run;

proc sql noprint;

select memname into : dsns separated by ', '

from dictionary.tables

where libname='WORK';

quit;

%put &dsns;

%MACRO Unite_Tables(&dsns)

%MEND;

Linlin

Re: Passing an arbitrary number of parameters to a macro

SASKiwi — Wed, 25 Jan 2012 01:25:57 GMT

I suggest you expand Linlin's example and put your list of tables into one macro variable. You can then pick up which table you want using %scan:

%let dataset_list = data1*data2*data3;

%let dataset2 = %scan(&dataset_list, 2,*);

%put &dataset2;

Re: Passing an arbitrary number of parameters to a macro

Tom — Wed, 25 Jan 2012 02:08:41 GMT

Use spaces instead of commas between the dataset names. Do not think of them as separate parameters but one parameter that takes a list of values.

%MACRO Unite_Tables(tablelist);

data united;

set &tablelist ;

run;

%MEND;

%unite_tables(t1 t2 t3);

Re: Passing an arbitrary number of parameters to a macro

Forgotthesemicolon — Wed, 25 Jan 2012 04:13:10 GMT

Wow thanks guys, I didn't expect a reply so soon

Those are all good ideas, in the end I went with Linlin & Saskiwi's suggestions. Here's the finished code:

%MACRO unite_tables(input_tables,output_table);

/* Read the number of rows in each table into variables. */

%LET num_input_tables = %EVAL(%SYSFUNC(COUNT(&input_tables.,*))+1);

%DO i=1 %TO &num_input_tables.;

%LET input_table_&i. = %SCAN(&input_tables.,&i.,*);

DATA Spine_&i.; SET &&input_table_&i..;

Spine=_N_;

CALL SYMPUT("num_rows_&i.",COMPRESS(_N_));

RUN;

%END;

/* Create a "spine" table to join the others on to. */

DATA Spine;

DO Spine=1 TO MAX(&num_rows_1.%DO i=1 %TO 2;,&&num_rows_&i.. %END;); OUTPUT; END;

RUN;

/* Unite tables. */

PROC SQL;

CREATE TABLE &output_table. AS

SELECT S.Spine

%DO i=1 %TO &num_input_tables.; , A&i..* %END;

FROM Spine S

%DO i=1 %TO &num_input_tables.; LEFT JOIN Spine_&i. A&i.

ON S.Spine = A&i..Spine %END; ;

RUN;

DATA &output_table.; SET &output_table.; DROP Spine; RUN;

%MEND unite_tables;

What it does it take a given group of tables and blindly attach them to each other horizontally (makes it easier to copy and paste multiple datasets into Excel or whatever for adhoc work)... I don't know if SAS has an inbuilt way of doing this so I've always just made macros for it.

Re: Passing an arbitrary number of parameters to a macro

Tom — Thu, 26 Jan 2012 00:33:25 GMT

Sounds like a lot of work to do something that you can do in a simple data step.

data want ;

merge sashelp.class sashelp.citiyr;

output;

call missing(of _all_);

run;

Passing an arbitrary number of parameters to a macro

Forgotthesemicolon — Sun, 29 Jan 2012 22:59:30 GMT

Oh thanks, I didn't know you could use a merge without specifying a variable to join on, that's a lot neater :smileysilly:

What does the call missing statement do?

Passing an arbitrary number of parameters to a macro

art297 — Sun, 29 Jan 2012 23:15:49 GMT

It sets values to missing. However, in this particular situation, I don't think it is needed.

Re: Passing an arbitrary number of parameters to a macro

Tom — Mon, 30 Jan 2012 00:18:05 GMT

You do not need the output/call missing in this case. I was thinking of the case when you are merging by variables and have different number of observations per BY group. In that case SAS will retain the value of the last member of the group from the dataset that has fewer observations.

Check out the difference in this example with a BY variable when you run it with or without the output and call missing statements.

data one ;
  set sashelp.class nobs=nobs;
  i = int(3*_n_/nobs);
run;
data two ;
  set sashelp.citiyr nobs=nobs;
  i = int(3*_n_/nobs);
run;
data want ;
   merge one two ;
   by i;
   output;
   call missing(of _all_);
run;