SQL Procedure

Example 9: Joining Three Tables

Features:

FROM clause

joined-table component

WHERE clause

Table names:

PROCLIB.STAFF2

PROCLIB.SCHEDULE2

PROCLIB.SUPERV2

Details

This example joins three tables and produces a report that contains columns from each table.

PROCLIB.STAFF2 Table

data proclib.staff2;
input IdNum $4. @7 Lname $12. @20 Fname $8. @30 City $10. 
      @42 State $2. @50 Hphone $12.;
   datalines;
1106  MARSHBURN    JASPER    STAMFORD    CT      203/781-1457
1430  DABROWSKI    SANDRA    BRIDGEPORT  CT      203/675-1647
1118  DENNIS       ROGER     NEW YORK    NY      718/383-1122
1126  KIMANI       ANNE      NEW YORK    NY      212/586-1229
1402  BLALOCK      RALPH     NEW YORK    NY      718/384-2849
1882  TUCKER       ALAN      NEW YORK    NY      718/384-0216
1479  BALLETTI     MARIE     NEW YORK    NY      718/384-8816
1420  ROUSE        JEREMY    PATERSON    NJ      201/732-9834
1403  BOWDEN       EARL      BRIDGEPORT  CT      203/675-3434
1616  FUENTAS      CARLA     NEW YORK    NY      718/384-3329
;
run;

proc sql;
   title 'PROCLIB.STAFF2';
   select * from proclib.staff2;
   title;

PROCLIB.STAFF2

PROCLIB.SCHEDULE2 Table

data proclib.schedule2;
   input flight $3. +5 date date7. +2 dest $3. +3 idnum $4.;
   format date date7.;
   informat date date7.;
   datalines;
132     01MAR94  BOS   1118
132     01MAR94  BOS   1402
219     02MAR94  PAR   1616
219     02MAR94  PAR   1478
622     03MAR94  LON   1430
622     03MAR94  LON   1882
271     04MAR94  NYC   1430
271     04MAR94  NYC   1118
579     05MAR94  RDU   1126
579     05MAR94  RDU   1106
;
run;

proc sql;
   title 'PROCLIB.SCHEDULE2';
   select * from proclib.schedule2;
   title;

PROCLIB.SCHEDULE2

PROCLIB.SUPERV2 Table

data proclib.superv2;
   input supid $4. +8 state $2. +5  jobcat  $2.;
   label supid='Supervisor Id' jobcat='Job Category';
   datalines;
1417        NJ     NA
1352        NY     NA
1106        CT     PT
1442        NJ     PT
1118        NY     PT
1405        NJ     SC
1564        NY     SC
1639        CT     TA
1126        NY     TA
1882        NY     ME
;
run;

proc sql;
   title 'PROCLIB.SUPERV2';
   select * from proclib.superv2
   title;

PROCLIB.SUPERV2

Program

libname proclib 'SAS-library';

proc sql;
   title 'All Flights for Each Supervisor';
   select s.IdNum, Lname, City 'Hometown', Jobcat,
          Flight, Date

from proclib.schedule2 s, proclib.staff2 t, proclib.superv2 v

where s.idnum=t.idnum and t.idnum=v.supid;

Program Description

Declare the PROCLIB library.The PROCLIB library is used in these examples to store created tables.

libname proclib 'SAS-library';

Select the columns.The SELECT clause specifies the columns to select. IdNum is prefixed with a table alias because it appears in two tables.

proc sql;
   title 'All Flights for Each Supervisor';
   select s.IdNum, Lname, City 'Hometown', Jobcat,
          Flight, Date

Specify the tables to include in the join.The FROM clause lists the three tables for the join and assigns an alias to each table.

from proclib.schedule2 s, proclib.staff2 t, proclib.superv2 v

Specify the join criteria.The WHERE clause specifies the columns that join the tables. The STAFF2 and SCHEDULE2 tables have an IdNum column, which has related values in both tables. The STAFF2 and SUPERV2 tables have the IdNum and SUPID columns, which have related values in both tables.

where s.idnum=t.idnum and t.idnum=v.supid;

Output

ID Values from All Three Tables Are Included