Using SAS SET statement with numbered macro variables

Question

I'm trying to create a custom transformation within SAS DI Studio to do some complicated processing which I will want to reuse often. In order to achieve this, as a first step, I am trying to replicate the functionality of a simple APPEND transformation.

To this end, I've enabled multiple inputs (max of 10) and am trying to leverage the &_INPUTn and &_INPUT_count macro variables referenced here. I would like to simply use the code

data work.APPEND_DATA / view=work.APPEND_DATA;
   %let max_input_index = %sysevalf(&_INPUT_count - 1,int);
   set &_INPUT0 - &&_INPUT&max_input_index;
   keep col1 col2 col3; 
run;

However, I receive the following error:

ERROR: Missing numeric suffix on a numbered data set list (WORK.SOME_INPUT_TABLE-WORK.ANOTHER_INPUT_TABLE)

because the macro variables are resolved to the names of the datasets they refer to, whose names do not conform to the format required for the

SET dataset1 - dataset9;

statement. How can I get around this?

Much gratitude.

Answer 1

You need to create a macro that loops through your list and resolves the variables. Something like

%macro list_tables(n);
   %do i=1 %to &n;
      &&_INPUT&i
   %end;
%mend;

data work.APPEND_DATA / view=work.APPEND_DATA;
   %let max_input_index = %sysevalf(&_INPUT_count - 1,int);
   set %list_tables(&max_input_index);
   keep col1 col2 col3; 
run;

Answer 2

The SET statement will need a list of the actual dataset names since they might not form a sequence of numeric suffixed names.

You could use a macro %DO loop if are already running a macro. Make sure to not generate any semi-colons inside the %DO loop.

set 
%do i=1 %to &_inputcount ; &&_input&i %end;
;

But you could also use a data step to concatenate the names into a single macro variable that you could then use in the SET statement.

data _null_;
  call symputx('_input1',symget('_input'));
  length str $500 ;
  do i=1 to &_inputcount;
    str=catx(' ',str,symget(cats('_input',i)));
  end;
  call symputx('_input',str);
run;
data .... ;
  set &_input ;
  ...

The extra CALL SYMPUTX() at the top of the data step will handle the case when count is one and SAS only creates the _INPUT macro variable instead of creating the series of macro variables with the numeric suffix. This will set _INPUT1 to the value of _INPUT so that the DO loop will still function.