Example: Use 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.

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-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. 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.

   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.)

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.