## Merging two datasets

Solved
Occasional Contributor
Posts: 17

# 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: 8,164

## 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;

All Replies
Posts: 1,147

## Re: Merging two datasets

data set3;

merge set1 set2;

by var3;

if first.var3=0 then do;

var1=.;

var2=.;

end;

run;

Thanks,

Jag

Thanks,
Jag
Super User
Posts: 6,774

## 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...

 m 1 10 a 2 15 t 12 5 a 3 45 w 3 6 a 4 52 . . a 10 11 . a 94 10 . . a 15 19 m 4 14 b 3 25 t 7 10 b 1 10 w 4 5 b 3 17 . . b 15 18 . . b 11 12 . . b 80 70 m 5 15 c 4 20 t 6 16 c 5 30 w 7 6 c 4 11 . . c 18 17 . . c 20 20 . . c 50 15

Solution
‎02-24-2014 07:53 PM
PROC Star
Posts: 8,164

## 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 and locked.