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 ;
<code></code><code></code><code></code>
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 Detroit123450 Wayne County 123400 Michigan 123000 USA
123456 Detroit 100000 Earth
134567 Bordeaux134560 Gironde 134500 Nouvelle-Aquitaine
;
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?
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.
Why do you stop at 123000 USA in the first record? This has parent 120000 North America?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.