BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jojo
Obsidian | Level 7

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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;

View solution in original post

8 REPLIES 8
Reeza
Super User

Why not include Append in macro directly? 

Reeza
Super User

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
Opal | Level 21

@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
Obsidian | Level 7

Thanks, changed to proc datasets delete

jojo
Obsidian | Level 7

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
Obsidian | Level 7

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

ballardw
Super User

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.

 

sas-innovate-2024.png

 

Time is running out to save with the early bird rate. Register by Friday, March 1 for just $695 - $100 off the standard rate.

 

Check out the agenda and get ready for a jam-packed event featuring workshops, super demos, breakout sessions, roundtables, inspiring keynotes and incredible networking events. 

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 8 replies
  • 4124 views
  • 3 likes
  • 5 in conversation