The SQL Procedure

Example 7: Performing an Outer Join

Procedure features:

joined-table component

left outer join

SELECT clause

COALESCE function

WHERE clause

CONTAINS condition

Tables:

PROCLIB.PAYROLL, PROCLIB.PAYROLL2

This example illustrates a left outer join of the PROCLIB.PAYROLL and PROCLIB.PAYROLL2 tables.

Input Tables

PROCLIB.PAYROLL (Partial Listing)

PROCLIB.PAYROLL2

Program

libname proclib 'SAS-data-library';

options nodate pageno=1 linesize=80 pagesize=60;

proc sql outobs=10;
   title 'Most Current Jobcode and Salary Information';
   select p.IdNumber, p.Jobcode, p.Salary,
          p2.jobcode label='New Jobcode',
          p2.salary label='New Salary' format=dollar8.

      from proclib.payroll as p left join proclib.payroll2 as p2

      on p.IdNumber=p2.idnum;

Output

As the output shows, all rows from the left table, PROCLIB.PAYROLL, are returned. PROC SQL assigns missing values for rows in the left table, PAYROLL, that have no matching values for IdNum in PAYROLL2.

title 'Most Current Jobcode and Salary Information'; select p.idnumber, coalesce(p2.jobcode,p.jobcode) label='Current Jobcode',

coalesce(p2.salary,p.salary) label='Current Salary' format=dollar8.

from proclib.payroll p left join proclib.payroll2 p2 on p.IdNumber=p2.idnum;

Output

title 'Most Current Information for Ticket Agents'; select p.IdNumber, coalesce(p2.jobcode,p.jobcode) label='Current Jobcode', coalesce(p2.salary,p.salary) label='Current Salary' from proclib.payroll p left join proclib.payroll2 p2 on p.IdNumber=p2.idnum where p2.jobcode contains 'TA';

Output

Chapter Contents
Previous
Next
Top of Page

As the output shows, all rows from the left table, PROCLIB.PAYROLL, are returned. PROC SQL assigns missing values for rows in the left table, PAYROLL, that have no matching values for IdNum in PAYROLL2.