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 this example, the CUSTBASE Oracle table 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 and generates this Oracle code:

select distinct custbase."STATE" from CUSTBASE

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

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