Hi I have a got two data sets and I have shown them below. I'm trying to merge them by duplicating one of the columns "A " in dataset 1 depending on the number of entries of column "B" in dataset 2 for the same ID.
Dataset 1
ID | A |
1234 | xyz |
1245 | abc |
1245 | xya |
4567 | abd |
4567 | xyz |
4567 | abc |
Dataset 2
ID | B |
1234 | 100-1 |
1234 | 112-4 |
1245 | 116-5 |
4567 | 45-8 |
4567 | 96-7 |
4567 | 152-7 |
After merging I would like the result to be as follows.
ID | A | B |
1234 | xyz | 100-1 |
1234 | xyz | 112-4 |
1245 | abc | 116-5 |
1245 | xya | 116-5 |
4567 | abd | 45-8 |
4567 | abd | 96-7 |
4567 | abd | 152-7 |
4567 | xyz | 45-8 |
4567 | xyz | 96-7 |
4567 | xyz | 152-7 |
4567 | abc | 45-8 |
4567 | abc | 96-7 |
4567 | abc | 152-7 |
I have tried the norming merging and interleaving but it doesn't work.
Does it require if the else and do looping to get this result ?
That looks like it's just a typographical error. Try:
on A.ID = B.ID
Other than that, the logic looks sound.
EDIT - Code corrected - your joining rule has duplicate key values in both input datasets so this is best done with SQL:
proc sql;
create table want as
select A.ID
,A.A
,B.B
from Dataset1 as A
inner join Dataset2 as B
on A.ID = B.ID
;
quit;
The above code gave me a cartisian product of the two data sets. Not the result which I was looking for.Thanks anyways.
That looks like it's just a typographical error. Try:
on A.ID = B.ID
Other than that, the logic looks sound.
Thank you, it worked.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.