Overview of In-Database Procedures

In-Database Procedure Considerations and Limitations

The considerations and limitations in the following sections apply to both Base SAS and SAS/STAT in-database procedures.

Note: Each in-database procedure has its own specific considerations and limitations. For more information, see the documentation for the procedure. [cautionend]

Row Order

DBMS tables have no inherent order for the rows. Therefore, the BY statement with the NOTSORTED option, the OBS option, and the FIRSTOBS option will prevent in-database processing.
The order of rows written to a database table from a SAS procedure is not likely to be preserved. For example, the SORT procedure can output a SAS data set that contains ordered observations. If the results are written to a database table, the order of rows within that table might not be preserved because the DBMS has no obligation to maintain row order.
You can print a table using the SQL procedure with an ORDER BY clause to get consistent row order or you can use the SORT procedure to create an ordinary SAS data set and use the PRINT procedure on that SAS data set.

BY-Groups

BY-group processing is handled by SAS for Base SAS procedures. Raw results are returned from the DBMS, and SAS BY-group processing applies formats as necessary to create the BY group.

For SAS/STAT procedures, formats can be applied, and BY-group processing can occur inside the DBMS if the SAS_PUT() function and formats are published to the DBMS. For more information, see the SAS Analytics Accelerator for Teradata: User's Guide.

The following BY statement option settings apply to the in-database procedures:

The DESCENDING option is supported.
The NOTSORTED option is not supported because the results are dependent on row order. DBMS tables have no inherent order for the rows.

By default, when SAS/ACCESS creates a database table, SAS/ACCESS uses the SAS formats that are assigned to variables to decide which DBMS data types to assign to the DBMS columns. If you specify the DBFMTIGNORE system option for numeric formats, SAS/ACCESS creates DBMS columns with a DOUBLE PRECISION data type. For more information, see the Overview of the LIBNAME Statement for Relational Databases, LIBNAME Statement Data Conversions, and DBFMTIGNORE= System Option.

LIBNAME Statement

These LIBNAME statement options and settings prevent in-database processing:
- DBMSTEMP=YES
- DBCONINIT
- DBCONTERM
- DBGEN_NAME=SAS
- PRESERVE_COL_NAMES=NO
- PRESERVE_TAB_NAMES=NO
- PRESERVE_NAMES=NO
- MODE=TERADATA
LIBNAME concatenation prevents in-database processing.

Data Set-related Options

These data set options and settings prevent in-database processing:

RENAME= on a data set.
OUT= data set on DBMS and DATA= data set not on DBMS.

For example, you can have data=td.foo and out=work.fooout where WORK is the Base SAS engine.
DATA= and OUT= data sets are the same DBMS table.
OBS= and FIRSTOBS= on DATA= data set.

Miscellaneous Items

These items prevent in-database processing:

DBMSs do not support SAS passwords.
SAS encryption requires passwords which are not supported.
Teradata does not support generation options that are explicitly specified in the procedure step, and the procedure does not know whether a generation number is explicit or implicit.
When the database resolves function references. the database searches in this order:
1. fully qualified object name
2. current database
3. SYSLIB
If you need to reference functions that are published in a nonsystem, nondefault database, you must use one of these methods:
- explicit SQL
- DATABASE= LIBNAME option
- map the fully qualified name (schema.sas_put) in the external mapping

Top of Page