topic Re: Merging two data sets involving duplicating of one of the columns. in SAS Programming

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

Ritu_Gowlikar — Tue, 18 Oct 2016 19:09:10 GMT

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 ?

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

LinusH — Tue, 18 Oct 2016 19:18:13 GMT

No, just a simple SQL join.

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

SASKiwi — Tue, 18 Oct 2016 20:26:08 GMT

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;

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

Ritu_Gowlikar — Tue, 18 Oct 2016 19:53:41 GMT

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

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

Astounding — Tue, 18 Oct 2016 20:06:24 GMT

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

on A.ID = B.ID

Other than that, the logic looks sound.

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

Ritu_Gowlikar — Tue, 18 Oct 2016 20:18:45 GMT

Thank you, it worked.