topic Re: merge/join three datasets in SAS Data Management

merge/join three datasets

perilipin — Mon, 04 Mar 2019 17:16:51 GMT

How do I put 3 variables from 3 different data together by id?

I have 3 different data with common id number but different variables.

Data1
id variable1
1 a
2 b
2 s
3 c

Data2
id variable2
1 d
1 e
2 f
3 g
3 h

Data3
id variable3
1 I
1 j
1 k
2 l
2 m
2 n
2 o
3 p
3 q
3 r

All I want is just putting these variables together by id in one dataset:

Desired Dataset
id variable1 variable2 variable3
1 a d I
1 . e j
1 . . k
2 b f l
2 s . m
2 . . n
2 . . o
3 c g p
3 . h q
3 . . r

How could I do that ? neither merge nor inner join works this way.

Re: merge/join three datasets

Astounding — Mon, 04 Mar 2019 17:25:40 GMT

With a small change, you can get MERGE to do this. Assuming your data sets are sorted:

data want;
merge data1 data2 data3;
by id;
output;
call missing(variable1, variable2, variable3);
run;

Re: merge/join three datasets

perilipin — Mon, 04 Mar 2019 17:59:17 GMT

Wow it works.

Thank you very much!