O'Reilly logo

SAS 9.4 SQL Procedure User's Guide, Fourth Edition, 4th Edition by SAS Institute

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required