Passing DISTINCT and UNION Processing to the DBMS

When you use the SAS/ACCESS LIBNAME statement to access DBMS data, the DISTINCT and UNION operators are processed in the DBMS rather than in SAS. For example, when PROC SQL detects a DISTINCT operator, it passes the operator to the DBMS to check for duplicate rows. The DBMS then returns only the unique rows to SAS.

In the following example, the Oracle table CUSTBASE is queried for unique values in the STATE column.

libname myoralib oracle user=testuser password=testpass;
proc sql;
   select distinct state from myoralib.custbase;
quit;

The DISTINCT operator is passed to Oracle. This generates the following Oracle code:

select distinct custbase."STATE" from CUSTBASE

Oracle then passes the results from this query back to SAS.

Copyright © 2007 by SAS Institute Inc., Cary, NC, USA. All rights reserved.