topic Re: Using DO loop to merge multiple datasets in SAS Programming

Using DO loop to merge multiple datasets

ybz12003 — Tue, 08 Sep 2020 17:09:07 GMT

Hello,

I am trying to use DO LOOP to merge multiple datasets with different names. But the coding is not working, please help. Thanks.

%let Y1=ulung;
%let Y2=uheme;
%let Y3=ucancer;
%let Y4=utransplant;
%let Y5=ugm;

%macro merge;
	%do i = 1 %to 5;
         data Underlying;
	 set &&Y&i.;
          run;
	
	%end;
%mend;
%merge;

Re: Using DO loop to merge multiple datasets

Kurt_Bremser — Tue, 08 Sep 2020 17:34:30 GMT

Your macro will create 5 data steps, all writing (and thereby overwriting) the same dataset. So in the end, dataset underlying will hold the contents of ugm.

From the code, I have no idea what you want to achieve, but it sure ain't a merge.

Re: Using DO loop to merge multiple datasets

Reeza — Tue, 08 Sep 2020 17:36:59 GMT

Are you trying to append data sets together or merge them - these are not the same things. MERGE means adding columns typically whereas an append stacks data.

If you trace your code through five loops you'll find it's overwriting itself all the time. So what are you trying to do here?

Re: Using DO loop to merge multiple datasets

ballardw — Tue, 08 Sep 2020 17:44:29 GMT

The set statement will accept multiple data sets.

data Underlying;
   set ulung uheme ucancer utransplant ugm;

So no macro needed.

If the sets are the only ones in your library that start with a common "stem" you can use the : list operator:

data underlying;
   set u: ;
run;

And if you really must have a macro this makes a SET statement like the first example above.

%macro merge;
	
   data Underlying;
      set 
      %do i = 1 %to 5;
        &&Y&i.
      %end;
      ; /*<= this ; ends the SET statement*/
   run;
	
%mend;

Since your macro was generating code like:

data want;

set a;

run;

data want;

set b;

run;

Do you see why your output was always the last data set? And you really need to provide a better description than "is not working".

It is often less than best practice to have macro variables appear in the body of a macro without being parameters. Otherwise it can be pretty hard to find where the values are coming from when "something is not working".

By the way, have you figured out how to use option MPRINT yet? I believe it has been suggested several times as a basic tool for debugging macro issues.

Re: Using DO loop to merge multiple datasets

ybz12003 — Tue, 08 Sep 2020 19:45:58 GMT

Thank you so much! I didn't know there is a tricky way to use 'U:' to save tying my 15 dataset names one by one.