DATA Step, Macro, Functions and more

Passing an arbitrary number of parameters to a macro

Reply
Occasional Contributor
Posts: 6

Passing an arbitrary number of parameters to a macro

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.

Respected Advisor
Posts: 3,799

Re: Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon
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>

Super Contributor
Posts: 1,636

Re: Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon

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

Super User
Posts: 3,260

Re: Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon

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;

Super User
Super User
Posts: 7,079

Re: Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon

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

Occasional Contributor
Posts: 6

Re: Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon

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

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.. %ENDSmiley Wink; 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.

Super User
Super User
Posts: 7,079

Re: Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon

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;

Occasional Contributor
Posts: 6

Passing an arbitrary number of parameters to a macro

Posted in reply to 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?

PROC Star
Posts: 7,492

Passing an arbitrary number of parameters to a macro

Posted in reply to Forgotthesemicolon

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

Super User
Super User
Posts: 7,079

Re: Passing an arbitrary number of parameters to a macro

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;

Ask a Question
Discussion stats
  • 9 replies
  • 1786 views
  • 1 like
  • 6 in conversation