SAS 9.4 SQL Procedure User's Guide, Third Edition, 3rd Edition

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;

Figure 7.11 Proclib.Schedule2

Example Code 7.3 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

294 Chapter 7 • SQL Procedure

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;

Figure 7.12 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.

Example 9: Joining Three Tables 295

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 each have an IdNum column, which enables a join on

rows where these column values match in both tables. The Staff2 and Superv2 tables

have the IdNum and SupId columns, which enable a join on rows where these column

values match in both tables. The combination of these two conditions enables the three

tables to be joined.

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

Output: Joining Three Tables

Output 7.13 All Flights for Each Supervisor

Example 10: Querying an In-Line View

Features: FROM clause

in-line view

Table names: Proclib.Staff2

Proclib.Schedule2

Proclib.Superv2

This example shows an alternative way to construct the query that is explained in

“Example 9: Joining Three Tables” on page 292 by joining one of the tables with the

296 Chapter 7 • SQL Procedure

results of an in-line view. The example also shows how to rename columns with an in-

line view.

Program

libname proclib 'SAS-library';

proc sql;

title 'All Flights for Each Supervisor';

select three.*, v.jobcat

from (select lname, s.idnum, city, flight, date

from proclib.schedule2 s, proclib.staff2 t

where s.idnum=t.idnum)

as three (Surname, Emp_ID, Hometown,

FlightNumber, FlightDate),

proclib.superv2 v

where three.Emp_ID=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 selects all columns that are returned by the in-

line view (which will have the alias Three assigned to it), plus one column from the third

table (which will have the alias V assigned to it).

proc sql;

title 'All Flights for Each Supervisor';

select three.*, v.jobcat

Specify the in-line query. Instead of including the name of a table or view, the FROM

clause includes a query that joins two of the three tables. In the in-line query, the

SELECT clause lists the columns to select. IdNum is prefixed with a table alias because

it appears in both tables. The FROM clause lists the two tables for the join and assigns

an alias to each table. The WHERE clause specifies the columns that join the tables. The

Staff2 and Schedule2 tables each have an IdNum column, which enables a join on rows

where these column values match in both tables.

from (select lname, s.idnum, city, flight, date

from proclib.schedule2 s, proclib.staff2 t

where s.idnum=t.idnum)

Specify an alias for the query and names for the columns. The alias Three refers to

the results of the in-line view. The names in parentheses become the names for the

columns in the view.

as three (Surname, Emp_ID, Hometown,

FlightNumber, FlightDate),

Join the results of the in-line view with the third table. The WHERE clause specifies

the columns that join the table with the in-line view. Note that the WHERE clause

specifies the renamed Emp_ID column from the in-line view.

Example 10: Querying an In-Line View 297