Optimizing Your SQL Usage

Passing Functions to the DBMS Using PROC SQL

When you use the SAS/ACCESS LIBNAME statement, it automatically tries to pass the SAS SQL aggregate functions (MIN, MAX, AVG, MEAN, FREQ, N, SUM, and COUNT) to the DBMS because these are SQL ANSI-defined aggregate functions.

Here is a sample query of the Oracle EMP table being passed to the DBMS for processing:

libname myoralib oracle user=testuser password=testpass;
proc sql;
   select count(*) from myoralib.emp;
quit;

This code causes Oracle to process this query:

select COUNT(*) from EMP

SAS/ACCESS can also translate other SAS functions into DBMS-specific functions so they can be passed to the DBMS.

In this next example, the SAS UPCASE function is translated into the Oracle UPPER function:

libname myoralib oracle user=testuser password=testpass;
proc sql;
   select customer from myoralib.customers
   where upcase(country)="USA";
quit;

Here is the translated query that is processed in Oracle:

select customer from customers where upper(country)='USA'

Functions that are passed are different for each DBMS. Select your DBMS to see a list of functions that your SAS/ACCESS interface translates.

Top of Page