Chapter 3 - Combining Tables Horizontally Using PROC SQL
Combining Tables By Using an Inner Join
proc sql outobs=15;
title 'New York Employees';
select substr(firstname,1,1) || '. ' || lastname
as Name,
jobcode,
int((today() - dateofbirth)/365.25)
as Age
from sasuser.payrollmaster as p,
sasuser.staffmaster as s
where p.empid =
s.empid
and state='NY'
order by 2, 3;
quit;
코드 설명:
Inner join 부분: where p.empid =
s.empid
Outer Join in SQL
-Left Join
proc sql;
select *
from one
left join
two
on one.x=two.x;
proc sql;
select one.x, a, b
from one
left join
two
on one.x=two.x;
위와 다른 이유: select one.x를 해서!
-Right Join
proc sql;
select *
from one
right join
two
on one.x=two.x;
-Full Join
proc sql;
select *
from one
full join
two
on one.x=two.x;
Full join 이후 생성되는 데이터 셋 left join과 right join의 명백한 차이점 확인:)
Combining Tables By Using a Left Outer Join
proc sql outobs=20;
title 'All March Flights';
select m.date,
m.flightnumber
label='Flight Number',
m.destination
label='Left',
f.destination
label='Right',
delay
label='Delay in Minutes'
from sasuser.marchflights as m
left join
sasuser.flightdelays as f
on m.date=f.date
and m.flightnumber=
f.flightnumber
order by delay;
quit;
코드 설명:
from sasuser.marchflights as m
left join
sasuser.flightdelays as f
on m.date=f.date
A left join B on A.변수=B.변수
A가 메인!
When Only Some of the Values Match: Using the COALESCE
coalesce사용하기 전의 outer join
proc sql;
title 'Table Merged';
select three.x, a, b
from three
full join
four
on three.x = four.x
order by x;
-> outerjoin임에도 select three.x을 해서 X에대해서는 공백이 나옴
이것을 해결하기 위해 Data step의 merge와 똑같은 결과 순서대로 내고 싶은 경우 활용되는 coalesce 기능!!!
proc sql;
title 'Table Merged';
select coalesce(three.x, four.x)
as X, a, b
from three
full join
four
on three.x = four.x;
data merged;
merge three four;
by x;
run; *merge해도 똑같은 결과 나옴!!!;
