Accessing DBMS Data with the LIBNAME Statement Querying Multiple DBMS Tables 249
Querying Multiple DBMS Tables
You can also retrieve data from multiple DBMS tables in a single query by using the
SQL procedure. This example joins the Oracle tables Staff and Payroll to query salary
information for employees who earn more than $40,000.
libname mydblib oracle user=testuser password=testpass;
title ’Employees with salary greater than $40,000’;
options obs=20;
proc sql;
select a.lname, a.fname, b.salary
format=dollar10.2
from mydblib.staff a, mydblib.payroll b
where (a.idnum eq b.idnum) and
(b.salary gt 40000);
quit;
Note: By default, SAS/ACCESS passes the entire join to the DBMS for processing in
order to optimize performance. See “Passing Joins to the DBMS” on page 34 for more
information.
Partial output for this example is shown here.
Output 13.8 Querying Multiple Oracle Tables
Employees with salary greater than $40,000
LNAME FNAME SALARY
--------------------------------------------
WELCH DARIUS $40,858.00
VENTER RANDALL $66,558.00
THOMPSON WAYNE $89,977.00
RHODES JEREMY $40,586.00
DENNIS ROGER $111379.00
KIMANI ANNE $40,899.00
O’NEAL BRYAN $40,079.00
RIVERS SIMON $53,798.00
COHEN LEE $91,376.00
GREGORSKI DANIEL $68,096.00
NEWKIRK WILLIAM $52,279.00
ROUSE JEREMY $43,071.00
The next example uses the SQL procedure to join and query the DB2 tables March,
Delay, and Flight. The query retrieves information on delayed international flights
during the month of March.
libname mydblib db2 ssid=db2;
title "Delayed International Flights in March";
proc sql;
select distinct march.flight, march.dates format datetime9.,
delay format=2.0
from mydblib.march, mydblib.delay,
mydblib.internat
