Merging two data sets involving duplicating of one of the columns.

[Ritu_Gowlikar]

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 ?

[LinusH]

No, just a simple SQL join.

[SASKiwi]

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;

[Ritu_Gowlikar]

The above code gave me a cartisian product of the two data sets. Not the result which I  was looking for.Thanks anyways.

[Astounding · Accepted solution]

That looks like it's just a typographical error.  Try:

on A.ID = B.ID

Other than that, the logic looks sound.

[Ritu_Gowlikar]

Thank you, it worked.