Overview of In-Database Procedures

Introduction to In-Database Procedures

In the second and third maintenance releases for SAS 9.2, the following Base SAS, SAS Enterprise Miner, SAS/ETS, and SAS/STAT procedures have been enhanced for in-database processing.

Procedures Enhanced for In-Database Processing
Procedure Name	DBMS Supported
CORR^*	Teradata
CANCORR^*	Teradata
DMDB^*	Teradata
DMINE^*	Teradata
DMREG^*	Teradata
FACTOR^*	Teradata
FREQ	Teradata, DB2 under UNIX and PC Hosts, Oracle
PRINCOMP^*	Teradata
RANK	Teradata, DB2 under UNIX and PC Hosts, Oracle
REG^*	Teradata
REPORT	Teradata, DB2 under UNIX and PC Hosts, Oracle
SCORE^*	Teradata
SORT	Teradata, DB2 under UNIX and PC Hosts, Oracle
SUMMARY/MEANS	Teradata, DB2 under UNIX and PC Hosts, Oracle
TABULATE	Teradata, DB2 under UNIX and PC Hosts, Oracle
TIMESERIES^*	Teradata
VARCLUS^*	Teradata
* SAS Analytics Accelerator is required to run these procedures inside the database. For more information, see SAS Analytics Accelerator for Teradata: Guide.

Using conventional processing, a SAS procedure (by means of the SAS/ACCESS engine) receives all the rows of the table from the database. All processing is done by the procedure. Large tables mean that a significant amount of data must be transferred.

Using the new in-database technology, the procedures that are enabled for processing inside the database generate more sophisticated queries that allow the aggregations and analytics to be run inside the database. Some of the in-database procedures generate SQL procedure syntax and use implicit pass-through to generate the native SQL. Other in-database procedures generate native SQL and use explicit pass-through. For more information about how a specific procedure works inside the database, see the documentation for that procedure.

The queries submitted by SAS in-database procedures reference DBMS SQL functions and, in some cases, the special SAS functions that are deployed inside the database. One example of a special SAS function is the SAS_PUT() function that enables you to execute PUT function calls inside Teradata. Other examples are SAS functions for computing sum-of-squares-and-crossproducts (SSCP) matrices.

For most in-database procedures, a much smaller result set is returned for the remaining analysis that is required to produce the final output. As a result of using the in-database procedures, more work is done inside the database and less data movement can occur. This could result in significant performance improvements.

This diagram illustrates the in-database procedure process.

Process Flow Diagram

[Process Flow Diagram]

Top of Page