Accessing DBMS Data with the Pass-Through Facility Using a Pass-Through Query in a Subquery 267
Note: When SAS data is joined to DBMS data through a Pass-Through query, PROC
SQL cannot optimize the query. In this case it is much more efficient to use a SAS/
ACCESS LIBNAME statement. Another way to increase efficiency is to extract the
DBMS data and place it in a new SAS data file, assign SAS indexes to the appropriate
variables, and join the two SAS data files.
Using a Pass-Through Query in a Subquery
The following example shows how to use a subquery that contains a Pass-Through
query. A subquery is a nested query and is usually part of a WHERE or HAVING
clause. Summary functions cannot appear in a WHERE clause, so using a subquery is
often a good technique. A subquery is contained in parentheses and returns one or
more values to the outer query for further processing.
In this example you create a PROC SQL view, Samples.AllEmp, based on SYBASE
data. SYBASE objects, such as table names and columns, are case sensitive. Database
identification statements and column names are converted to uppercase unless they are
enclosed in quotes.
The outer PROC SQL query retrieves data from the PROC SQL view; the subquery
uses a Pass-Through query to retrieve data. This query returns the names of employees
who earn less than the average salary for each department. The macro variable, Dept,
substitutes the department name in the query.
libname mydblib sybase server=server1 database=personnel
user=testuser password=testpass;
libname samples ’your-SAS-data-library’;
/* create PROC SQL view */
proc sql;
create view samples.allemp as
select * from mydblib.employees;
quit;
/* use the Pass-Through Facility to retrieve data */
proc sql stimer;
title "Employees Who Earn Below the &dept Average Salary";
connect to sybase(server=server1 database=personnel
user=testuser password=testpass);
%put &sqlxmsg;
%let dept=’ACC%’;
select empid, lastname
from samples.allemp
where dept like &dept and salary <
(select avg(salary) from connection to sybase
(select SALARY from EMPLOYEES
where DEPT like &dept));
%put &sqlxmsg;
disconnect from sybase;
