BookmarkSubscribeRSS Feed
Forgotthesemicolon
Calcite | Level 5

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.

9 REPLIES 9
data_null__
Jade | Level 19
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
Lapis Lazuli | Level 10

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
PROC Star

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
Super User Tom
Super User

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
Calcite | Level 5

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.. %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
Super User Tom
Super User

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
Calcite | Level 5

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
Opal | Level 21

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

Tom
Super User Tom
Super User

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;

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
  • 9 replies
  • 5597 views
  • 1 like
  • 6 in conversation