Passing an arbitrary number of parameters to a macro

[Forgotthesemicolon]

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.

[data_null__]

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>

[Linlin]

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

[SASKiwi]

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;

[Tom]

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

[Forgotthesemicolon]

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.

[Tom]

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;

[Forgotthesemicolon]

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?

[art297]

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

[Tom]

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;