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.

For example, the following query of the Oracle table EMP is 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 the following 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 the following example, the SAS function UPCASE is translated into the Oracle function UPPER:

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

The translated query that is processed in Oracle is

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.

• DB2 UNIX/PC

• DB2 z/OS

• Greenplum

• HP Neoview

• Informix

• Microsoft SQL Server

• MySQL

• Netezza

• ODBC

• OLE DB

• Oracle

• Sybase

• Sybase IQ

• Teradata

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