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 that pass-through can pass them to the DBMS.

In this 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 Oracle processes.

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

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