Let’s illustrate different types of joins:
Full Join:
Data: Test
Data: MonthlyData
proc sql;
CREATE TABLE test3 AS
select test2.id, test2.Month,
MonthlyData.name, MonthlyData.sex, MonthlyData.DOB
from test2 full join MonthlyData
on test2.Month=MonthlyData.Month
and test2.id=MonthlyData.id
order by id, Month; quit;
test3:
You will get the same results if you do it the following ways:
Option 1:
proc sql;
CREATE TABLE test3 AS
select test2.id, test2.Month,
MonthlyData.name, MonthlyData.sex, MonthlyData.DOB
from test2 left join MonthlyData
on test2.Month=MonthlyData.Month
and test2.id=MonthlyData.id
order by id, Month; quit;
Option 2:
proc sql;
CREATE TABLE test3 AS
select MonthlyData.name, MonthlyData.sex, MonthlyData.DOB,
test2.id, test2.Month
from MonthlyData right join test2
on test2.Month=MonthlyData.Month
and test2.id=MonthlyData.id
order by id, Month; quit;
What if we rearrange the syntax:
proc sql;
CREATE TABLE test_3 AS
select MonthlyData.name, MonthlyData.sex, MonthlyData.DOB,
test2.id, test2.Month
from MonthlyData left join test2
on test2.Month=MonthlyData.Month
and test2.id=MonthlyData.id
order by id, Month; quit;
test_3: