topic appending data in iteration in SAS Data Management

appending data in iteration

jojo — Sun, 11 Jun 2017 19:32:29 GMT

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;

Re: appending data in iteration

Kurt_Bremser — Sun, 11 Jun 2017 19:37:14 GMT

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

Re: appending data in iteration

Reeza — Sun, 11 Jun 2017 21:18:30 GMT

Why not include Append in macro directly?

Re: appending data in iteration

Reeza — Sun, 11 Jun 2017 23:27:36 GMT

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;

Re: appending data in iteration

Patrick — Mon, 12 Jun 2017 00:58:06 GMT

@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.

Re: appending data in iteration

ballardw — Mon, 12 Jun 2017 14:16:45 GMT

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.

Re: appending data in iteration

jojo — Tue, 13 Jun 2017 15:46:47 GMT

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

Re: appending data in iteration

jojo — Tue, 13 Jun 2017 15:47:57 GMT

Thanks, changed to proc datasets delete

Re: appending data in iteration

jojo — Tue, 13 Jun 2017 17:51:07 GMT

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