268 Using a Pass-Through Query in a Subquery Chapter 14
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 14.3 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:
%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 on the PROC SQL
statement provides statistics on the SAS process.)
