Proc SQL II 1.6

Search the site...

Sentry Page Protection

Proc SQL II [6-17]

Left Outer Joins

Left Outer Joins join two tables by including all of the matching rows across the tables, as well as the non-matching rows from the left table.

Let's again look at the MATH and ENGLISH tables we created in the last section.

Copy and run the code from the yellow box below:

Data Math;
Input Name $ MathScore;
Datalines;
Ada 80
Jon 78
Arya 85
;
Run;

Data English;
Input Name $ EngScore;
Datalines;
Ada 83
Jon 66
Gary 90
;
Run;

The MATH and ENGLISH tables contain the exam results for the math and English classes.

Let's do a left outer join on the two tables.

Proc sql;
select *
from math as m left join english as e
on m.name = e.name;
quit;

The keywords left join are specified between the two tables in the FROM clause:

The output table contains the matching rows across the two tables (i.e. Ada and Jon):

The non-matching row from the MATH table (i.e. Arya) is also included:

Similar to the full outer join, you can use the COALESCE function to overlap the matching column(s).

Example

Proc sql;
select
coalesce(m.name, e.name) as name,
mathscore, engscore
from math as m left join english as e
on m.name = e.name;
quit;

The COALESCE function overlays the two NAME columns, and the output table contains a single NAME column only:

Exercise

Copy and run the code from the yellow box below:

Data MedRecord;
Input Patient $ Age;
Datalines;
PAT1001 18
PAT1002 28
PAT1003 52
PAT1004 41
PAT1005 60
;
Run;

Data Risk;
Input RangeLow RangeHigh Risk : $13.;
Datalines;
0 30 LowRisk
31 50 MediumRisk
51 120 HighRisk
;
Run;

The code above creates two tables:

Table 1: MEDRECORD

The MEDRECORD table contains a list of five patients and their age.

Table 2: RISK

The RISK table contains the risk of having a certain disease for three different age ranges.

The RANGELOW and RANGEHIGH columns represent the lower and upper limit of the age range for a particular risk category.

For example, a patient who is 28 years old falls into the first risk category, and the risk of having a certain disease is categorized as "LowRisk".

Write a Proc SQL step to join the MEDRECORD table with the RISK table. Match the patient’s age with the correct risk category.

Need some help?

HINT:
This question is a little tricky. You will have to compare the patient's age with the age ranges in the joining conditions.

SOLUTION:

proc sql;
select *
from medrecord left join risk
on age>rangelow and age<rangehigh;
quit;

Fill out my online form.