The SQL Procedure

Example 4: Joining Two Tables

Procedure features:

FROM clause

table alias

inner join

joined-table component

PROC SQL statement option

NUMBER

WHERE clause

IN condition

Tables:

PROCLIB.STAFF, PROCLIB.PAYROLL

This example joins two tables in order to get more information about data that are common to both tables.

Input Tables

                                 PROCLIB.STAFF
                               First 10 Rows Only

  Id
  Num   Lname            Fname            City             State  Hphone
  ----------------------------------------------------------------------------
  1919  ADAMS            GERALD           STAMFORD         CT     203/781-1255
  1653  ALIBRANDI        MARIA            BRIDGEPORT       CT     203/675-7715
  1400  ALHERTANI        ABDULLAH         NEW YORK         NY     212/586-0808
  1350  ALVAREZ          MERCEDES         NEW YORK         NY     718/383-1549
  1401  ALVAREZ          CARLOS           PATERSON         NJ     201/732-8787
  1499  BAREFOOT         JOSEPH           PRINCETON        NJ     201/812-5665
  1101  BAUCOM           WALTER           NEW YORK         NY     212/586-8060
  1333  BANADYGA         JUSTIN           STAMFORD         CT     203/781-1777
  1402  BLALOCK          RALPH            NEW YORK         NY     718/384-2849
  1479  BALLETTI         MARIE            NEW YORK         NY     718/384-8816

                                PROCLIB.PAYROLL
                               First 10 Rows Only

              Id
              Number  Gender  Jobcode    Salary    Birth    Hired
              ---------------------------------------------------
              1919    M       TA2         34376  12SEP60  04JUN87
              1653    F       ME2         35108  15OCT64  09AUG90
              1400    M       ME1         29769  05NOV67  16OCT90
              1350    F       FA3         32886  31AUG65  29JUL90
              1401    M       TA3         38822  13DEC50  17NOV85
              1499    M       ME3         43025  26APR54  07JUN80
              1101    M       SCP         18723  06JUN62  01OCT90
              1333    M       PT2         88606  30MAR61  10FEB81
              1402    M       TA2         32615  17JAN63  02DEC90
              1479    F       TA3         38785  22DEC68  05OCT89

Program

libname proclib 'SAS-library';

options nodate pageno=1 linesize=120 pagesize=40;

proc sql number;

    title 'Information for Certain Employees Only';

    select Lname, Fname, City, State,
           IdNumber, Salary, Jobcode

       from proclib.staff, proclib.payroll

       where idnumber=idnum and idnum in 
              ('1919', '1400', '1350', '1333');

Output: Listing

                 Information for Certain Employees Only

                                                    Id
    Row  Lname      Fname        City        State  Number   Salary  Jobcode
    ------------------------------------------------------------------------
    1  ADAMS        GERALD       STAMFORD    CT     1919     34376   TA2
    2  ALHERTANI    ABDULLAH     NEW YORK    NY     1400     29769   ME1
    3  ALVAREZ      MERCEDES     NEW YORK    NY     1350     32886   FA3
    4  BANADYGA     JUSTIN       STAMFORD    CT     1333     88606   PT2

Top of Page