Passing the WHERE Clause to the DBMS

General Guidelines for WHERE Clauses

Efficient WHERE clause processing can reduce the data movement between SAS and the DBMS, and SAS/ACCESS passes WHERE clauses to the DBMS whenever possible. When a WHERE clause is passed to the DBMS, the DBMS subsets the data, resulting in less data to be transferred between the DBMS and SAS. The performance improvement can be significant when you access large DBMS tables.

Follow the general guidelines in this table for writing efficient WHERE clauses.

Efficient WHERE Clause Guidelines
Guideline	Inefficient	Efficient
Avoid the NOT operator if you can use an equivalent form.	Inefficient: `where zipcode not>8000`	Efficient: `where zipcode<=8000`
Avoid LIKE predicates that begin with % or _ .	Inefficient: `where COUNTRY like '%INA'`	Efficient: `where COUNTRY like 'A%INA'`
Avoid arithmetic expressions in a predicate.	Inefficient: `where SALARY>12*4000.00`	Efficient: `where SALARY>48000.00`
Use DBINDEX=, DBKEY=, and MULTI_DATASRC_OPT= when appropriate.

If you have NULL values in a DBMS column that is used in a WHERE clause, be aware that your results might differ depending on whether the WHERE clause is processed in SAS or is passed to the DBMS for processing. This is because DBMSs tend to remove NULL values from consideration in a WHERE clause, but SAS does not. For more information and strategies for this situation, see Using the DIRECT_SQL= LIBNAME Option.

Passing Functions to the DBMS Using WHERE Clauses

When you use the SAS/ACCESS LIBNAME statement, SAS/ACCESS translates several SAS functions in WHERE clauses into DBMS-specific functions so that they can be passed to the DBMS.

In this SAS code, SAS can translate the FLOOR function into a DBMS function and pass the WHERE clause to the DBMS.

libname myoralib oracle user=myusr1 password=mypwd1;
proc print data=myoralib.personnel;
   where floor(hourlywage)+floor(tips)<10;
run;

Generated SQL that the DBMS processes would be similar to this code.

SELECT "HOURLYWAGE", "TIPS" FROM PERSONNEL
   WHERE ((FLOOR("HOURLYWAGE") + FLOOR("TIPS")) < 10)

If the WHERE clause contains a function that SAS cannot translate into a DBMS function, SAS retrieves all rows from the DBMS and applies the WHERE clause.

The functions that are passed down are different for each DBMS. See the information about passing functions to the DBMS for your SAS/ACCESS interface to determine which functions SAS/ACCESS translates.

Last updated: February 3, 2026