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.

If you're going to loop anyways to append, you may want to consider looping with proc append. Depending on data size it will be faster since it does not process the data line by line. — Reeza, May 18 '17 at 03:19
Okay, I will look into that approach. I had initially built the append with a SAS DI premade transformation, then when I went to convert the job code into a custom transformation I merely copy pasted the auto-generated code from the append. Not sure why SAS is doing things this way rather than with a proc append, but I'll strive for efficiency! — Rookatu, May 18 '17 at 14:51

DomPazz · Accepted Answer · 2017-05-17T17:59:09.653

2

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;

edited May 17 '17 at 17:59

answered May 17 '17 at 17:42

DomPazz

12,415
17
23

This worked except that the input to the macro function should have the '&' at the beginning to work. A clever solution from the perspective of someone unfamiliar with macro code. Thanks very much! – Rookatu May 17 '17 at 17:57
Good catch on the missing `&`. Edited and fixed! – DomPazz May 17 '17 at 17:59

Tom · Answer 2 · 2017-05-17T18:44:45.113

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.

Using SAS SET statement with numbered macro variables

2 Answers2