PROC SQL Tutorial 7 - outer join 1

outer join 에는 세가지가 있음

left, right, full

 

문법은 select ~ from ~ left join|right join|full join ~ on 으로 해결함

 

left outer join

 

proc sql;

select *

from one

left join

two

on one.x = two.x;

 

right outer join

 

proc sql;

select *

from one

right join

two

on one.x=two.x;

 

full outer join

 

proc sql;

select *

from one

full join

two

on one.x=two.x;

data merged;

merge three four;

by x;

run;

 

proc sql;

select three.x, a, b

from three

full join

four

on three.x = four.x

order by x;

coalesce 명령문을 같이 쓰면 해결

 

proc sql;

select coalesce(three.x, four.x) as X, a , b

from three

full join

four

on three.x = four.x;

 

sql join은 정렬이 필요없고, 변수명이 같지 않아도 되고, where 절에서 = 뿐만 아니라 다른 연산 > 등도 쓸 수 있다.