macro/loop to concatenate multiple datasets - proc append?

Accepted Solution Solved
Reply
Contributor
Posts: 42
Accepted Solution

macro/loop to concatenate multiple datasets - proc append?

[ Edited ]

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!


Accepted Solutions
Solution
‎01-22-2018 07:34 AM
Super User
Posts: 8,590

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎01-22-2018 07:34 AM
Super User
Posts: 8,590

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

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;
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 169

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

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!

Super Contributor
Posts: 429

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

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;

or

data results;
set result_period_:;
run;

 

Super User
Super User
Posts: 8,634

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

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 145 views
  • 2 likes
  • 5 in conversation