SAS/ACCESS Software for Relational Databases: Reference

Using a Pass-Through Query in a Subquery

The next 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. A subquery is contained in parentheses and returns one or more values to the outer query for further processing.

Note: This example uses a Version 6 view descriptor with the Pass-Through Facility to access DBMS data. Beginning in Version 7, you can associate a libref directly with your DBMS data and use the libref in your Pass-Through query just as you would use any SAS data set. As a result, you can now create a PROC SQL view, DATA step view, or SAS/ACCESS view with DBMS data. [cautionend]

For this example you create a view descriptor, VLIB.ALLEMP, based on SYBASE data. The outer PROC SQL query retrieves data from the view descriptor; 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. You can use the macro variable, DEPT, to substitute the department name more easily in the query.

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.

proc access dbms=sybase;

/* create access descriptor  */

   create work.employee.access;
   server=server1;
   database=personnel;
   user=carmen;
   password=aria;
   table=employees;

/* create vlib.allemp view  */

   create vlib.allemp.view;
   select all;
   format empid 6.0
          salary dollar12.2
          jobcode 5.0
          hiredate date9.
          birthdate date9. ;
   list all;
run;

proc sql stimer;
title "Employees Who Earn Below the &dept Average
Salary";

connect to sybase(server=server1 
  database=personnel user=carmen
  password=aria);
%put &sqlxmsg;

%let dept='ACC%';

select empid, lastname, firstnam
   from vlib.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 SYBASE 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 enclose 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:

select empid, lastname, firstnam
   from vlib.allemp
   where dept like &dept and salary < 57840.86;

Summary functions cannot appear in a WHERE clause, so using a subquery is often a good technique.

Employees who earn less than the department's average salary are returned in Output from a Pass-Through Query in a Subquery.

Output from a Pass-Through Query in a Subquery

Employees Who Earn Below the 'ACC%' Average Salary EMPID LASTNAME FIRSTNAME ------------------------------------------- 123456 VARGAS CHRIS 135673 HEMESLY STEPHANIE 423286 MIFUNE YUKIO 457232 LOVELL WILLIAM

In this example, it might appear to be more direct to omit the Pass-Through query and just to access VLIB.ALLEMP a second time in the subquery, as if the query were written:

%let dept='ACC%';

proc sql stimer;
select empid, lastname, firstnam
   from vlib.allemp
   where dept like &dept and salary <
         (select avg(salary)
             from vlib.allemp
             where dept like &dept);
quit;

However, as the SAS log in SAS Log Comparing the Two PROC SQL Queries 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 System's process.)

SAS Log Comparing the Two PROC SQL Queries

213 214 %let dept='ACC%'; 215 216 select empid, lastname, firstnam 217 from vlib.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 vlib.allemp 232 where dept like &dept and salary < 233 (select avg(salary) 234 from vlib.allemp 235 where dept like &dept); NOTE: The SQL Statement used 0:00:06.0 real 0:00:00.20 cpu.

Chapter Contents
Previous
Next
Top of Page