SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ss59
Obsidian | Level 7

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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;

View solution in original post

4 REPLIES 4
Kurt_Bremser
Super User

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;
s_lassen
Meteorite | Level 14

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!

andreas_lds
Jade | Level 19

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;

 

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

sas-innovate-white.png

Special offer for SAS Communities members

Save $250 on SAS Innovate and get a free advance copy of the new SAS For Dummies book! Use the code "SASforDummies" to register. Don't miss out, May 6-9, in Orlando, Florida.

 

View the full agenda.

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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