Temporary Table Support for SAS/ACCESS

Overview

DBMS temporary table support in SAS consists of the ability to retain DBMS temporary tables from one SAS step to the next. This ability is a result of establishing a SAS connection to the DBMS that persists across multiple SAS procedures and DATA steps.

Temporary table support is available for these DBMSs.

DBMS-Specific Temporary Table Support

DBMS

Temporary Table Support

DBMSTEMP= LIBNAME Support

Aster

yes

yes

DB2

yes

yes

Greenplum

yes

yes

Hadoop

yes

yes

Impala

no

no

Informix

yes

no

JDBC

yes

yes

Microsoft SQL Server

yes

yes

MySQL

yes

yes

Netezza

yes

yes

ODBC

yes

yes

OLE DB

yes

yes

Oracle

yes

yes

PostgreSQL

yes

yes

SAP ASE

yes

no1

SAP HANA

yes

yes

SAP IQ

yes

yes

Snowflake

yes

yes

Spark

no

no

Teradata

yes

yes

Vertica

yes

yes

Yellowbrick

yes

yes
1 The SAP ASE engine uses the same special table-name syntax as the native database to create a temporary table.

The value of DBMS temporary table support in SAS is increased performance potential. By pushing the processing to the DBMS in certain situations, you can achieve an overall performance gain. The processes in this section provide a general outline of how to use DBMS temporary tables.

General Temporary Table Use

Follow these steps to use temporary tables on the DBMS.

  1. Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.
  2. Create a DBMS temporary table and load it with data.
  3. Use the DBMS temporary table with SAS.

Closing the global connection causes the DBMS temporary table to close as well.

Pushing Heterogeneous Joins

Follow these steps to push heterogeneous joins to the DBMS.

  1. Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.
  2. Create a DBMS temporary table and load it with data.
  3. Perform a join on the DBMS using the DBMS temporary and DBMS permanent tables.
  4. Process the result of the join with SAS.

Pushing Updates

To push updates (process transactions) to the DBMS:

  1. Establish a global connection to the DBMS that persists across SAS procedure and DATA step boundaries.
  2. Create a DBMS temporary table and load it with data.
  3. Issue SQL that uses values in the temporary table to process against the production table.
  4. Process the updated DBMS tables with SAS.

Although these processing scenarios are purposely generic, they apply to each DBMS that supports temporary tables. For details, see the DBMSTEMP= LIBNAME Statement Option.

Last updated: February 3, 2026