appending data in iteration

[jojo]

Hi I want to append output data from a macro and repeat this process 100 times, how to implement this in SAS? Thank you!

%macro app(n=);

%do i =1 %to &n;

%getdata(a=,b=,c=); **** get "outdat" here for each i;

Proc append base=outdat data=finaldat  ; *** need to append the "outdat" from %getdata each, so the 100 "outdat" are appended to "finaldat";

%mend app;

[Kurt_Bremser]

If you want to append outdat to finaldat, you need to use base=finaldat data=outdat in the proc append statement.

[Reeza]

Why not include Append in macro directly? 

[Reeza · Accepted solution]

You're relatively close, as indicated you need some small changes. 

1. Add a drop table at beginning of process to remove master table first. If you have a specific structure requried that may not be maintained in process, ie variable lengths/formats you could make a master table with the structure desired but empty.

2. Switch names in Append as indicated by @Kurt_Bremser

3. Add a drop table for your generated data. You don't want to accidentally duplicate appending the results somehow. 

4. Missing %END for your %DO.

%macro app(n=);

*drop master table before loops in case of re-runs, optional;
proc sql noprint;
    drop table finaldat;
quit;

%do i =1 %to &n;

%getdata(a=,b=,c=); **** get "outdat" here for each i;

Proc append base=finaldat data=outdat ;
run;

*remove dataset so that you do not accidentally append multiple times;
proc sql noprint;
   drop table outdat;
quit; 

%end; *missed this for the end of your loop;

%mend app;

[Patrick]

@jojo

Proposed small change to the code @Reeza posted: 

Instead of a Proc SQL DROP...

proc sql noprint;
    drop table finaldat;
quit;

...use a Proc DATASETS DELETE with option NOWARN

proc datasets lib=WORK nolist nowarn;
  delete finaldat;
  run;
quit;

Reason:

Proc SQL DROP will throw an ERROR in case table FINALDAT doesn't exists - which is likely the case the first time you run your macro.

Proc DATASETS with option NOWARN on the other hand will deal nicely with cases where there isn't a table to be deleted.

[jojo]

Thanks, changed to proc datasets delete

[jojo]

Thanks very much, Reeza. For some reason, my final data has only one record when I ran the app with n>=1. Any suggestions?

[jojo]

Thanks all. I found the error and fixed the program (the i was used in the macro and was greater to &n). 

[ballardw]

Note that if your data sets you want to combine have a common and unique start to their name you may not need this at all to combine your data sets as you can use a name list. Suppose all of the data sets you want start with the PDQ.

data finaldata;

   set PDQ:  ;

run;

Note the colon, that says to use all the sets that start with PDQ. So PDQ101 PDQabc and PDQ___57 would all be combined.