DATA Step, Macro, Functions and more

Merging two datasets

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

Merging two datasets

I would like some help understanding how  merge works/how to adequately apply it to my data. Please see my code below and the desired results I am trying to achieve. Thank you.

data set1;

  input var1 var2 var3$;

  datalines;

1 10 a

4 14 b

5 15 c

run;

data set2;

  input var4 var5 var3$;

  datalines;

2 15 a

3 45 a

4 52 a

3 25 b

1 10 b

3 17 b

4 20 c

5 30 c

4 11 c

run;

data set3;

merge set1 set2;

by var3;

run;

/* Actual results*/

1 10 a 2 15

1 10 a 3 45

1 10 a 4 52

4 14 b 3 25

4 14 b 1 10

4 14 b 3 17

5 15 c 4 20

5 15 c 5 30

5 15 c 4 11

/*desired results*/

1 10   a 2 15

.   .   a  3 45

. .     a  4 52

4 14  b  3 25

. .     b 1 10

. .    b 3 17

5 15 c 4 20

.   .   c 5 30

.   .   c 4 11


Accepted Solutions
Solution
‎02-24-2014 07:53 PM
PROC Star
Posts: 7,363

Re: Merging two datasets

You could use something like:

data set1;

  input var0$ var1 var2 var3$;

  datalines;

m  1 10 a

t  12 5 a

w 3 6 a

m 4 14 b

t    7 10 b

w  4 5 b

m 5 15 c

t 6  16 c

w 7 6 c

;

 

data set2;

  input var4 var5 var3$;

  datalines;

2 15 a

3 45 a

4 52 a

10 11 a

94 10 a

15 19 a

3 25 b

1 10 b

3 17 b

15 18 b

11 12 b

80 70 b

4 20 c

5 30 c

4 11 c

18 17 c

20 20 c

50 15 c

;

data set1;

  set set1;

  by var3;

  if first.var3 then counter=1;

  else counter+1;

run;

data set2;

  set set2;

  by var3;

  if first.var3 then counter=1;

  else counter+1;

run;

data want (drop=counter);

  merge set1 set2;

  by var3 counter;

run;

View solution in original post


All Replies
Trusted Advisor
Posts: 1,128

Re: Merging two datasets

Please try the below code;

data set3;

merge set1 set2;

by var3;

if first.var3=0 then do;

var1=.;

var2=.;

end;

run;

Thanks,

Jag

Thanks,
Jag
Super User
Posts: 5,082

Re: Merging two datasets

The solution looks fine.  A few notes ...

You may find this paper useful:  http://www.ats.ucla.edu/stat/sas/library/nesug99/ad155.pdf

Also you may not need to blank out the variables for printing purposes.  Even PROC PRINT can produce a report without repeating VAR1 and VAR2.  For example, you can experiment with this:

proc print data=set3;

   by var3 var1 var2;

   id var3 var1 var2;

   var var4 var5;

run;

Good luck.

Occasional Contributor
Posts: 17

Re: Merging two datasets

my final code is using proc report, I was able to use the id on most of my variables but one of my variables keeps grouping as it doesn't have unique values.

Occasional Contributor
Posts: 17

Re: Merging two datasets

Thank you! This worked except I am more concerned about the last variable continually repeating itself.so a better example would be.

data set1;

  input var0$ var1 var2 var3$;

  datalines;

m  1 10 a

t  12 5 a

w 3 6 a

m 4 14 b

t    7 10 b

w  4 5 b

m 5 15 c

t 6  16 c

w 7 6 c

run;

data set2;

  input var4 var5 var3$;

  datalines;

2 15 a

3 45 a

4 52 a

10 11 a

94 10 a

15 19 a

3 25 b

1 10 b

3 17 b

15 18 b

11 12 b

80 70 b

4 20 c

5 30 c

4 11 c

18 17 c

20 20 c

50 15 c

run;

with the desire results being...

m110a215
t125a345
w36a452
. .a1011
    .a9410
..a1519
m414b325
t710b110
w45b317
    ..b1518
..b1112
..b8070
m515c420
t616c530
w76c411
..c1817
..c2020
   ..c5015

Thank you for your help

Solution
‎02-24-2014 07:53 PM
PROC Star
Posts: 7,363

Re: Merging two datasets

You could use something like:

data set1;

  input var0$ var1 var2 var3$;

  datalines;

m  1 10 a

t  12 5 a

w 3 6 a

m 4 14 b

t    7 10 b

w  4 5 b

m 5 15 c

t 6  16 c

w 7 6 c

;

 

data set2;

  input var4 var5 var3$;

  datalines;

2 15 a

3 45 a

4 52 a

10 11 a

94 10 a

15 19 a

3 25 b

1 10 b

3 17 b

15 18 b

11 12 b

80 70 b

4 20 c

5 30 c

4 11 c

18 17 c

20 20 c

50 15 c

;

data set1;

  set set1;

  by var3;

  if first.var3 then counter=1;

  else counter+1;

run;

data set2;

  set set2;

  by var3;

  if first.var3 then counter=1;

  else counter+1;

run;

data want (drop=counter);

  merge set1 set2;

  by var3 counter;

run;

☑ This topic is SOLVED.

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

Discussion stats
  • 5 replies
  • 263 views
  • 0 likes
  • 4 in conversation