Despite having to sort every data set, merging is providing the desired result for me. Below is the code I tried to compare results between proc sql and data step merging. /*created 10 data sets*/
data temp1;
input id x1 x2;
cards;
1 25 37
2 35 .
3 44 97
;
run;
data temp2;
input id var1 var2;
cards;
2 65 37
3 . 47
5 34 97
;
run;
data temp3;
input id xx1 xx2;
cards;
3 55 37
5 25 47
4 . 97
;
run;
data temp4;
input id var3 var4;
cards;
6 25 37
7 35 47
9 . .
;
run;
data temp5;
input id var5 var6;
cards;
5 65 37
6 85 47
9 34 97
;
run;
data temp6;
input id xx3 xx4;
cards;
11 55 37
13 25 47
14 64 97
;
run;
data temp7;
input id xx5 xx6;
cards;
11 25 .
15 35 47
16 44 97
;
run;
data temp8;
input id x3 x4;
cards;
12 65 .
15 85 47
16 . 97
;
run;
data temp9;
input id x5 x6;
cards;
17 55 37
18 25 47
19 64 97
;
run;
data temp10;
input id x7 x8;
cards;
17 . .
20 25 37
22 24 97
;
run;
/*not including the proc sort here */
data new;
merge temp1 temp2 temp3 temp4 temp5 temp6 temp7 temp8 temp9 temp10;
by id;
run;
proc print data = new;
title 'merged 10 data sets';
run; The above code produces below output, which seems like what I want. But the PROC SQL output looks messed up. I ran the same code for joining 3 tables and then the output was fine. But for 10 tables the output looks very weird. I am just experimenting with proc sql and want to know how to produce similar output like the data step merge. title "proc sql full join";
proc sql;
select coalesce(a.id, b.id, c.id, d.id, e.id, f.id, g.id, h.id) as idno, a.*, b.*, c.*, d.*, e.*, f.*, g.*, h.* , i.*, j.*
from temp1 a Full Join temp2 b on a.id = b.id
Full Join temp3 c on b.id = c.id
Full Join temp4 d on c.id = d.id
Full Join temp5 e on d.id = e.id
Full Join temp6 f on e.id = f.id
Full Join temp7 g on f.id = g.id
Full Join temp8 h on g.id = h.id
Full Join temp9 i on h.id = i.id
Full Join temp10 j on i.id = j.id;
quit; and the output is below.The idno column is not sorted in ascending order and it is also omitting some of the id numbers from 17,18,19,20,22 . Also idno 5 is repeated twice. Can you please tell me what's wrong here ?
... View more
