topic Appending dataset in Macro loop in SAS Programming

Appending dataset in Macro loop

anilgvdbm — Wed, 06 Dec 2017 10:37:44 GMT

Dear All,

I'm trying to append the 100 files from test001-test100;

when i run the below code

%macro append_all;
%do i=001 %to 194;
proc append base=final_mar data=test&i force; run;
%end;
%mend append_all;
%append_all;

the appending starts only from test100 file to test194... below 100 files are not appending what may be the reason?

Regards,

Anil

Re: Appending dataset in Macro loop

RW9 — Wed, 06 Dec 2017 10:43:24 GMT

This question is asked a lot, and the root cause of it is mismanaged programming before this step. How did you get 100 of datasets? Whats the betting that you have done some sort of loop over some data and for each by group you have created a new dataset for output. Your now stuck with a ton of datasets to append back together. The simplest method is not to fight the programming language, use by grouping for ultimate performance and simpler coding. For instance, if you were doing means like this:

...
  %do i=1 %to 100;
    proc means data=...
      where bvar=&i.;
      output out=want&i.;
    run;
   %end;
...

You would end up with 100 datasets all much the same, and then need to work with those. You could use by grouping to vastly simplfy:

proc means data=...
  by bvar;
  output out=want;
run;

This produces one dataset, with all the first codes output information, grouped by the bvar variable. You can then further process this without need to append lots of data.

Re: Appending dataset in Macro loop

error_prone — Wed, 06 Dec 2017 11:07:33 GMT

The leading zeros are automatically removed, because numbers don't have leading zeros.

Best way to solve the issue is following @RW9 posts. If you can't fix the problem, try this:

%macro appending;
   %local dsn;
   
   %do dsn = 001 %to 194;
      %let dsn = %sysfunc(putn(&dsn, z3.));
      proc append base=work.complete new=work.a&dsn;
      run;
   %end;
%mend;

Re: Appending dataset in Macro loop

ballardw — Wed, 06 Dec 2017 16:13:08 GMT

Here is an example of what @error_prone means about the values of your index variable:

%macro dummy;
%do i=001 %to 194;
   %put macro variable i resolves to: &i;
%end;
%mend;
%dummy;

The log will show what &i is as seen when resolved in loop.

Re: Appending dataset in Macro loop

Reeza — Wed, 06 Dec 2017 16:33:48 GMT

data want;
set test001-test194;
run;