DATA Step, Macro, Functions and more

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

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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

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                              

IDA
1234xyz
1245abc
1245xya
4567abd
4567xyz
4567abc

 

Dataset 2

IDB
1234100-1
1234112-4
1245116-5
456745-8
456796-7
4567152-7

 

After merging I would like the result to be as follows.

IDAB
1234xyz100-1
1234xyz112-4
1245abc116-5
1245xya116-5
4567abd45-8
4567abd96-7
4567abd152-7
4567xyz45-8
4567xyz96-7
4567xyz152-7
4567abc45-8
4567abc96-7
4567abc152-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 ?

 

 


Accepted Solutions
Solution
‎10-18-2016 04:18 PM
Super User
Posts: 5,080

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

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

 

on A.ID = B.ID

 

Other than that, the logic looks sound.

View solution in original post


All Replies
Super User
Posts: 5,256

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

No, just a simple SQL join.
Data never sleeps
Super User
Posts: 3,102

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

[ Edited ]

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;

 

New Contributor
Posts: 3

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

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

Solution
‎10-18-2016 04:18 PM
Super User
Posts: 5,080

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

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

 

on A.ID = B.ID

 

Other than that, the logic looks sound.

New Contributor
Posts: 3

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

Thank you, it worked.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 319 views
  • 1 like
  • 4 in conversation