Sas Table Joins Tutorial

SAS Table Joins Tutorial & Demonstrations – Tom Tuduc Inner Join, Right Join, Left Join, and Full Join. This tutorial demonstrates various table joins in SAS with SQL codes and results. Sample Data Table Measurement PersonId 1 3 4 5 6

Weight 133 123 98 155 158

Table Patient PersonId Name 1 Mike J. 2 Ann K. 3 Trish L. 7 Tom K 9 Lance T.

Height 5.7 5.4 5.5 5.9 6.1

Gender m f f m m

City Portland Salem Eugene Portland Portland

INNER JOIN

proc sql; select Patient.PersonID, Height, Name, Gender from Measurement, Patient where Measurement.PersonId = Patient.PersonId; quit;

Results: PersonId 1 3

Height 5.7 5.4

Name Mike J. Trish L.

Gender m f

RIGHT JOIN, LEFT JOIN, and FULL JOIN proc sql; title " Performing Right Join"; select p.PersonId as Patient_PersonId , m.PersonId as Measurement_PersonId, Height from learn.Patient as p right join learn.Measurement as m on p.PersonId eq m.PersonId; title " Performing Left Join";

select p.PersonId as Patient_PersonId , m.PersonId as Measurement_PersonId, Height from learn.Patient as p left join learn.Measurement as m on p.PersonId eq m.PersonId; title " Performing Full Join"; select p.PersonId as Patient_PersonId , m.PersonId as Measurement_PersonId, Height from learn.Patient as p full join learn.Measurement as m on p.PersonId eq m.PersonId; quit;

Results: Performing Right Join Patient_PersonId

Measurement_PersonId

Height

1 3 4 5 6

1 3

5.7 5.4 5.5 5.9 6.1

Patient_PersonId

Measurement_PersonId

Height

1

1 2 3 7 9

5.7

Measurement_PersonId

Height

Performing Left Join

3

5.4

Performing Full Join Patient_PersonId

1

5.7 2

3 4 5 6

5.4 5.5 5.9 6.1 7 9