SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition

Figure 7.7 Proclib.Payroll

proc sql;

title 'Proclib.Payroll2';

select * from proclib.payroll2

order by idnum;

title;

284 Chapter 7 • SQL Procedure

Figure 7.8 Proclib.Payroll2

Program Using OUTER JOIN Based on ID Number

libname proclib 'SAS-library';

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;

Program Description

Declare the Proclib library. The Proclib library is used in these examples to store

created tables.

libname proclib 'SAS-library';

Limit the number of output rows. OUTOBS= limits the output to 10 rows.

proc sql outobs=10;

Specify the title for the first query.

title 'Most Current Jobcode and Salary Information';

Example 7: Performing an Outer Join 285

Select the columns. The SELECT clause lists the columns to select. Some column

names are prefixed with a table alias because they are in both tables. LABEL= and

FORMAT= are column modifiers.

select p.IdNumber, p.Jobcode, p.Salary,

p2.jobcode label='New Jobcode',

p2.salary label='New Salary' format=dollar8.

Specify the type of join. The FROM clause lists the tables to join and assigns table

aliases. The keywords LEFT JOIN specify the type of join. The order of the tables in the

FROM clause is important. Proclib.Payroll is listed first and is considered the “left”

table. Proclib.Payroll2 is the “right” table.

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

Specify the join criterion. The ON clause specifies that the join be performed based on

the values of the ID numbers from each table.

on p.IdNumber=p2.idnum;

Output: OUTER JOIN Based on ID Number

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.

Output 7.9 Most Current Jobcode and Salary Information

Program Using COALESCE and LEFT JOIN

proc sql outobs=10;

title 'Most Current Jobcode and Salary Information';

286 Chapter 7 • SQL Procedure

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;

Program Description

proc sql outobs=10;

Specify the title for the second query.

title 'Most Current Jobcode and Salary Information';

Select the columns and coalesce the Jobcode columns. The SELECT clause lists the

columns to select. COALESCE overlays the like-named columns. For each row,

COALESCE returns the first nonmissing value of either P2.JobCode or P.JobCode.

Because P2.JobCode is the first argument, if there is a nonmissing value for P2.JobCode,

COALESCE returns that value. Thus, the output contains the most recent job code

information for every employee. LABEL= assigns a column label.

select p.idnumber, coalesce(p2.jobcode,p.jobcode)

label='Current Jobcode',

Coalesce the Salary columns. For each row, COALESCE returns the first nonmissing

value of either P2.Salary or P.Salary. Because P2.Salary is the first argument, if there is a

nonmissing value for P2.Salary, then COALESCE returns that value. Thus, the output

contains the most recent salary information for every employee.

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

format=dollar8.

Specify the type of join and the join criterion. The FROM clause lists the tables to join

and assigns table aliases. The keywords LEFT JOIN specify the type of join. The ON

clause specifies that the join is based on the ID numbers from each table.

from proclib.payroll p left join proclib.payroll2 p2

on p.IdNumber=p2.idnum;

Example 7: Performing an Outer Join 287

Output: COALESCE and LEFT JOIN

Output 7.10 Most Current Jobcode and Salary Information

Program to Subset the Query

proc sql;

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';

Program Description

Subset the query. The WHERE clause subsets the left join to include only those rows

containing the value TA.

proc sql;

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';

288 Chapter 7 • SQL Procedure