SAS day 38
SAS Merge is a good data step to join couple datasets together, nonetheless, SQL join is a fancier one, not necessarily easy to use, but definitely more sophisticated!
rawpixel / Pixabay
Today we will show a SQL Full Join with 3 datasets, respectively, a, b, c.
dataset a
dataset b
dataset c
Full join for Two Datasets:
proc sql noprint;
create table all as select distinct a.c1, b.c1 as c2,
coalescec(a.lbl,b.lbl) as lbl,
coalesce(a.ord,b.ord) as ord,
coalesce(a.bigord,b.bigord) as bigord
from all9 as a full join all10 as b on a.bigord=b.bigord and a.lbl=b.lbl
order by calculated bigord,calculated ord;
quit;
SQL Full Join a anb b
Full join for Three Datasets:
proc sql noprint;
create table all as select distinct a.c1, b.c1 as c2, c.c1 as c3,
coalescec(a.lbl,b.lbl,c.lbl) as lbl,
coalesce(a.ord,b.ord, c.ord) as ord,
coalesce(a.bigord,b.bigord, c.bigord) as bigord
from all9 as a full join all10 as b on a.bigord=b.bigord and a.lbl=b.lbl
full join all11 as c on a.bigord=c.bigord and a.lbl=c.lbl
order by calculated bigord,calculated ord;
quit;
SQL full join a , b, and c
Caution:
Common Mistake:
Full join could NOT do 3 datasets at once!
Bad Example:
from all9 as a full join all10 as b full join all11 as c on a.bigord=b.bigord=c.bigord and a.lbl=b.lbl=c.lbl