DATA Step, Macro, Functions and more

Appending dataset in Macro loop

Accepted Solution Solved
Reply
Contributor
Posts: 68
Accepted Solution

Appending dataset in Macro loop

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


Accepted Solutions
Solution
‎12-12-2017 04:18 AM
Regular Contributor
Posts: 195

Re: Appending dataset in Macro loop

Posted in reply to anilgvdbm

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;

View solution in original post


All Replies
Super User
Super User
Posts: 9,227

Re: Appending dataset in Macro loop

Posted in reply to anilgvdbm

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.

Solution
‎12-12-2017 04:18 AM
Regular Contributor
Posts: 195

Re: Appending dataset in Macro loop

Posted in reply to anilgvdbm

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;
Super User
Posts: 13,084

Re: Appending dataset in Macro loop

Posted in reply to anilgvdbm

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.

 

Super User
Posts: 22,874

Re: Appending dataset in Macro loop

Posted in reply to anilgvdbm
data want;
set test001-test194;
run;
☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 461 views
  • 4 likes
  • 5 in conversation