Accessing DBMS Data with the SQL Pass-Through Facility |
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.
This example creates an 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 quotation marks.
The outer PROC SQL query retrieves data from the 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 SQL view */ proc sql; create view samples.allemp as select * from mydblib.employees; quit; /* Use the SQL 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; quit;
When a PROC SQL query contains subqueries or inline views, the innermost query is evaluated first. In this example, data is retrieved from the Employees table and returned to the subquery for further processing. Notice that the Pass-Through query is enclosed in parentheses (in italics) and another set of parentheses encloses the entire subquery.
When a comparison operator such as < or > is used in a WHERE clause, the subquery must return a single value. In this example, the AVG summary function returns the average salary of employees in the department, $57,840.86. This value is inserted in the query, as if the query were written:
where dept like &dept and salary < 57840.86;
Employees who earn less than the department's average salary are listed in the following output.
Output from a Pass-Through Query in a Subquery
Employees Who Earn Below the 'ACC%' Average Salary EMPID LASTNAME ----------------- 123456 VARGAS 135673 HEMESLY 423286 MIFUNE 457232 LOVELL
It might appear to be more direct to omit the Pass-Through query and to instead access Samples.AllEmp a second time in the subquery, as if the query were written as follows:
%let dept='ACC%'; proc sql stimer; select empid, lastname from samples.allemp where dept like &dept and salary < (select avg(salary) from samples.allemp where dept like &dept); quit;
However, as the SAS log below indicates, the PROC SQL query with the Pass-Through subquery performs better. (The STIMER option in the PROC SQL statement provides statistics on the SAS process.)
SAS Log Comparing the Two PROC SQL Queries
213 214 %let dept='ACC%'; 215 216 select empid, lastname, firstnam 217 from samples.allemp 218 where dept like &dept and salary < 219 (select avg(salary) 220 from connection to sybase 221 (select SALARY from EMPLOYEES 222 where DEPT like &dept)); NOTE: The SQL Statement used 0:00:00.2 real 0:00:00.20 cpu. 223 %put &sqlxmsg; 224 disconnect from sybase; NOTE: The SQL Statement used 0:00:00.0 real 0:00:00.0 cpu. 225 quit; NOTE: The PROCEDURE SQL used 0:00:00.0 real 0:00:00.0 cpu. 226 227 %let dept='ACC%'; 228 229 proc sql stimer; NOTE: The SQL Statement used 0:00:00.0 real 0:00:00.0 cpu. 230 select empid, lastname, firstnam 231 from samples.allemp 232 where dept like &dept and salary < 233 (select avg(salary) 234 from samples.allemp 235 where dept like &dept); NOTE: The SQL Statement used 0:00:06.0 real 0:00:00.20 cpu.
Copyright © 2010 by SAS Institute Inc., Cary, NC, USA. All rights reserved.