topic How do I iterate using a do loop to merge a one-to-many? in SAS Programming

How do I iterate using a do loop to merge a one-to-many?

jungjein — Tue, 26 Nov 2019 11:58:38 GMT

I have the following datasets which I want to match data from have_a with have_b by the id name.

data have_a;
input id name :$40.;
cards;123456 Detroit
134567 Bordeaux;

data have_b;
input id name :&$40. parent_id parent_name :&$40.;
cards;123456  Detroit             123450  Wayne County
123450  Wayne County        123400  Michigan
123400  Michigan            123000  USA
123000  USA                 120000  North America
120000  North America       100000  Earth
134567  Bordeaux            134560  Gironde
134560  Gironde             134500  Nouvelle-Aquitaine
123456  Detroit             100000  Earth ;

I want it to iterate a one-to-many merge so that the final output will be something like:

data final;
input id name id1 name1 id2 name2 :$40.;
cards;

123456 Detroit    123450  Wayne County    123400  Michigan    123000  USA

123456 Detroit    100000 Earth

134567 Bordeaux 134560  Gironde    134500  Nouvelle-Aquitaine ;

Re: How do I iterate using a do loop to merge a one-to-many?

PeterClemmensen — Tue, 26 Nov 2019 11:25:49 GMT

Just to be clear, your wanted data set looks like this, right?

data final;
input id name :&$40. id1 name1 :&$40. id2 name2 :$40.;
cards;
123456 Detroit    123450 Wayne County  123400 Michigan           
123000 USA        123456 Detroit       100000 Earth              
134567 Bordeaux   134560 Gironde       134500 Nouvelle-Aquitaine 
;

What is the logic here?

Re: How do I iterate using a do loop to merge a one-to-many?

jungjein — Tue, 26 Nov 2019 12:02:25 GMT

Hi, sorry, I think the formatting was screwed.

It should be like this:

data final;
input id name :&$40. id1 name1 :&$40. id2 name2 :$40. id3 name3 :$40.;
cards;
123456 Detroit    123450 Wayne County  123400 Michigan           123000 USA        
123456 Detroit       100000 Earth              
134567 Bordeaux   134560 Gironde       134500 Nouvelle-Aquitaine 
;

The id in have_a and id in have_b are used to match to find the id1 and name1.

After which, I want to merge id1 with id in have_b and the iteration continues.

Re: How do I iterate using a do loop to merge a one-to-many?

PeterClemmensen — Tue, 26 Nov 2019 13:07:55 GMT

Why do you stop at 123000 USA in the first record? This has parent 120000 North America?