SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

appending data in iteration

Accepted Solution Solved
Reply
Contributor
Posts: 50
Accepted Solution

appending data in iteration

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;

 


Accepted Solutions
Solution
‎06-13-2017 01:48 PM
Super User
Posts: 19,822

Re: appending data in iteration

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

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;

View solution in original post


All Replies
Super User
Posts: 7,824

Re: appending data in iteration

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 19,822

Re: appending data in iteration

Why not include Append in macro directly? 

Solution
‎06-13-2017 01:48 PM
Super User
Posts: 19,822

Re: appending data in iteration

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

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;
Respected Advisor
Posts: 4,173

Re: appending data in iteration

[ Edited ]

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

Contributor
Posts: 50

Re: appending data in iteration

Thanks, changed to proc datasets delete

Contributor
Posts: 50

Re: appending data in iteration

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

Contributor
Posts: 50

Re: appending data in iteration

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

Super User
Posts: 11,343

Re: appending data in iteration

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.

 

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 222 views
  • 2 likes
  • 5 in conversation