Proc Sql Full Join

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

Happy Practice !!!

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Social media & sharing icons powered by UltimatelySocial
%d bloggers like this: