34 Passing Functions to the DBMS Using PROC SQL Chapter 5
Passing Functions to the DBMS Using PROC SQL
When you use the SAS/ACCESS LIBNAME statement, the SAS SQL aggregate
functions MIN, MAX, AVG, MEAN, FREQ, N, SUM, and COUNT are passed to the
DBMS (because they 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’
The functions that are passed are different for each DBMS. See the documentation
for your SAS/ACCESS interface to determine which functions it translates.
Passing Joins to the DBMS
When you perform a join across tables in a single DBMS, SAS/ACCESS can often pass
the join to the DBMS for processing. Before implementing a join, PROC SQL checks to
see whether the DBMS can do the join. A comparison is made using the SAS/ACCESS
LIBNAME statement for the tables. Certain criteria must be met for the join to proceed.
If it can, PROC SQL passes the join to the DBMS, which performs the join and
returns only the results to SAS. If the DBMS cannot do the join, PROC SQL processes
the join.
The following types of joins are eligible for passing to the DBMS:
for all DBMSs, inner joins between two or more tables.
for DBMSs that support ANSI outer join syntax, outer joins between two or more
DBMS tables.
for Informix (which has nonstandard outer join syntax), outer joins of two tables.
