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
                               First 10 Rows Only

              Id
              Number  Gender  Jobcode    Salary    Birth    Hired
              ---------------------------------------------------
              1009    M       TA1         28880  02MAR59  26MAR92
              1017    M       TA3         40858  28DEC57  16OCT81
              1036    F       TA3         39392  19MAY65  23OCT84
              1037    F       TA1         28558  10APR64  13SEP92
              1038    F       TA1         26533  09NOV69  23NOV91
              1050    M       ME2         35167  14JUL63  24AUG86
              1065    M       ME2         35090  26JAN44  07JAN87
              1076    M       PT1         66558  14OCT55  03OCT91
              1094    M       FA1         22268  02APR70  17APR91
              1100    M       BCK         25004  01DEC60  07MAY88

                                PROCLIB.PAYROLL2

                 Id
                 Num   Sex  Jobcode    Salary    Birth    Hired
                 ----------------------------------------------
                 1036  F    TA3         42465  19MAY65  23OCT84
                 1065  M    ME3         38090  26JAN44  07JAN87
                 1076  M    PT1         69742  14OCT55  03OCT91
                 1106  M    PT3         94039  06NOV57  16AUG84
                 1129  F    ME3         36758  08DEC61  17AUG91
                 1221  F    FA3         29896  22SEP67  04OCT91
                 1350  F    FA3         36098  31AUG65  29JUL90
                 1369  M    TA3         36598  28DEC61  13MAR87
                 1447  F    FA1         22123  07AUG72  29OCT92
                 1561  M    TA3         36514  30NOV63  07OCT87
                 1639  F    TA3         42260  26JUN57  28JAN84
                 1998  M    SCP         23100  10SEP70  02NOV92

Program Using OUTER JOIN Based on ID Number

libname proclib 'SAS-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: Listing

                  Most Current Jobcode and Salary Information

                  Id                         New           New
                  Number  Jobcode    Salary  Jobcode    Salary
                  --------------------------------------------
                  1009    TA1         28880                  .
                  1017    TA3         40858                  .
                  1036    TA3         39392  TA3       $42,465
                  1037    TA1         28558                  .
                  1038    TA1         26533                  .
                  1050    ME2         35167                  .
                  1065    ME2         35090  ME3       $38,090
                  1076    PT1         66558  PT1       $69,742
                  1094    FA1         22268                  .
                  1100    BCK         25004                  .

Program Using COALESCE and LEFT JOIN

   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: Listing

                  Most Current Jobcode and Salary Information

                           Id      Current   Current
                           Number  Jobcode    Salary
                           -------------------------
                           1009    TA1       $28,880
                           1017    TA3       $40,858
                           1036    TA3       $42,465
                           1037    TA1       $28,558
                           1038    TA1       $26,533
                           1050    ME2       $35,167
                           1065    ME3       $38,090
                           1076    PT1       $69,742
                           1094    FA1       $22,268
                           1100    BCK       $25,004

Program to Subset the Query

   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: Listing

                   Most Current Information for Ticket Agents

                           Id      Current   Current
                           Number  Jobcode    Salary
                           -------------------------
                           1036    TA3         42465
                           1369    TA3         36598
                           1561    TA3         36514
                           1639    TA3         42260

Top of Page