Variable number of parameters in macro in SAS?

Question

I have a macro where I am currently passing in 6 table names and 6 columns. However, the number of columns and tables will not always be constant.

Is there a way to have a variable number of parameters? I am familiar with the concept in python with **kwargs.

Also, is there a way to parameterize the proc sql statement to only take as many col and table inputs as provided? Or do a try catch of some sort in SAS to check if the variables exist before running the sql statement?

Here is my macro I'm trying to parameterize.

%macro Generate_TP_tbl(new_tbl_name, trans_col, tbl_1, tbl_2, tbl_3, tbl_4, 
                    tbl_5, tbl_6, col_1, col_2, col_3, col_4, col_5, col_6);

proc sql;

CREATE TABLE &new_tbl_name AS 

SELECT a1._NAME_, a1.&trans_col as &col_1, a2.&trans_col as &col_2, 
       a3.&trans_col as &col_3, a4.&trans_col as &col_4, a5.&trans_col as &col_5, 
       a6.&trans_col as &col_6

FROM &tbl_1 as a1, &tbl_2 as a2, &tbl_3 as a3, &tbl_4 as a4, &tbl_5 as a5, 
     &tbl_6 as a6 

WHERE a1._NAME_ = a2._NAME_ = a3._NAME_ = a4._NAME_ = a5._NAME_ = a6._NAME_;

run;

%mend Generate_TP_table;

Use spaces instead of commas in your lists. Then you need just one or two parameters. — Tom

user667489 user667489 · Accepted Answer · 2016-11-17T17:35:35

An even more generic way of doing this is as follows:

%macro mymacro /parmbuff;
  %put &SYSPBUFF;
%mend;

You can then call %mymacro with any parameters you like and parse them all out from the &SYSPBUFF automatic macro variable.

This would probably need more work than Reeza's solution would, but I thought I'd post this anyway for completeness, as it's occasionally useful.

Variable number of parameters in macro in SAS?

2 Answers