Previous Page | Next Page

The SQL Procedure

Example 4: Joining Two Tables

Procedure features:

FROM clause

table alias

inner join

joined-table component

PROC SQL statement option


WHERE clause

IN condition


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

Input Tables

                               First 10 Rows Only

  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

                               First 10 Rows Only

              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


 Note about code
libname proclib 'SAS-library';
 Note about code
options nodate pageno=1 linesize=120 pagesize=40;
 Note about code
proc sql number;
 Note about code
    title 'Information for Certain Employees Only';
 Note about code
    select Lname, Fname, City, State,
           IdNumber, Salary, Jobcode
 Note about code
       from proclib.staff, proclib.payroll
 Note about code
       where idnumber=idnum and idnum in 
              ('1919', '1400', '1350', '1333');

Output: Listing

                 Information for Certain Employees Only

    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

Previous Page | Next Page | Top of Page