topic Re: macro/loop to concatenate multiple datasets - proc append? in SAS Programming

macro/loop to concatenate multiple datasets - proc append?

ss59 — Mon, 22 Jan 2018 12:08:22 GMT

Hi,

I have a very simple problem, but it's bugging me. I have multiple datasets with same number of columns and column name that I want to merge vertically. The following code works:

data results;
set result_period_1 result_period_2 result_period_3;
run;

However, total no of periods is a lot, and I want to have a simple macro/loop that achieves the same thing. I tried proc append, but didn't work:

%macro append;
      proc append base=results_period_1 data=
   %do  i =  1 %to 3;
         results_period_&i
   %end;
 run;
%mend append;

%append;

Appreciate your help!

Re: macro/loop to concatenate multiple datasets - proc append?

Kurt_Bremser — Mon, 22 Jan 2018 12:18:25 GMT

Why not use the same datastep code as base for your macro?

%macro append;
data results;
set
%do  i =  1 %to 3;
  results_period_&i
%end;
;
run;
%mend append;

%append;

Or use call execute():

data _null_;
call execute('data results; set');
do i = 1 to 3;
  call execute(' results_period_' !! strip(put(i,best.)));
end;
call execute('; run;');
run;

Re: macro/loop to concatenate multiple datasets - proc append?

s_lassen — Mon, 22 Jan 2018 12:19:46 GMT

You cannot append more than one dataset at a time; you will have to have one PROC APPEND invocation for each table, e.g.:

%macro append;
  %local i;
  %do  i =  1 %to 3;
    proc append base=results data=results_period_&i;
    run;
    %end;
%mend append;

%append;

I changed the BASE to RESULTS, your original code looked like it was going to append results_period_1 to itself (you can do that, but that's probably not what you want). If the BASE table does not exist initially, it will be created, no problem about that. You may even want to put in a PROC DELETE DATA=RESULTS in before the APPEND loop, in case you run the code several times.

And you will save yourself a lot of trouble in the long run by declaring local macro variables %LOCAL!

Re: macro/loop to concatenate multiple datasets - proc append?

andreas_lds — Mon, 22 Jan 2018 12:21:37 GMT

Untested, be sure that "results" does not exist:

%macro append;
   %do  i =  1 %to 3;
      proc append base=results data=results_period_&i.;
      run;
   %end;
%mend append;

%append;

data results;
set result_period_:;
run;

Re: macro/loop to concatenate multiple datasets - proc append?

RW9 — Mon, 22 Jan 2018 13:33:18 GMT

Why do you have multiple datasets all the same, but for different periods in the first place? It is rarely if ever that splitting data up doe sanything more than multiply up the resources needed, and the cost or writing lots of code. SAS provides something called by group processing to exactly this task, with minimal coding/maintenance, and low resource costs so best to use that. So fix the steps before the data results, to instead of being some big loop creating lots of datasets, to one dataset which has period as a variable and then do:

by period;

On your steps.