O'Reilly logo

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

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required