Specifies whether SAS creates temporary or permanent tables.
| Valid in: | SAS/ACCESS LIBNAME statement |
|---|---|
| Category: | Data Set Control |
| Default: | NO |
| Requirement: | To specify DBMSTEMP=YES, you must also specify CONNECTION=GLOBAL for all data sources except Microsoft SQL Server. For Microsoft SQL Server, temporary tables can be accessed by different connections, so the default value CONNECTION=UNIQUE can be used. |
| Interaction: | To access temporary tables, set DBMSTEMP= to YES and set the CONNECTION= LIBNAME option to GLOBAL. |
| Data source: | Amazon Redshift, Aster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, Hadoop, HAWQ, JDBC, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, PostgreSQL, SAP HANA, SAP IQ, Snowflake, Spark, Teradata, Vertica, Yellowbrick |
| Notes: | Support for Spark was added in SAS 9.4M7. |
| Support for Yellowbrick was added in SAS 9.4M7. | |
| See: | CONNECTION= LIBNAME option, Temporary Table Support for SAS/ACCESS |
Table of Contents
specifies that SAS creates one or more temporary tables.
specifies that SAS creates permanent tables.
To significantly improve performance, you must also specify DBCOMMIT=0. The value for SCHEMA= is ignored. You can then access and use the DBMS temporary tables using SAS/ACCESS engine librefs that share the global connection that SAS used to create those tables.
To join a temporary table and a permanent table, you need a libref for each table and these librefs must successfully share a global connection.
Oracle: Set INSERTBUFF=1000 or higher to significantly improve performance.
ODBC: This engine supports DB2, MS SQL Server, or Oracle if you are connected to them.
This example shows how to use this option to create a permanent and temporary table and then join them in a query. The temporary table might not exist beyond a single PROC step. However, this might not be true for all DBMSs.
options sastrace=(,,d,d) nostsuffix sastraceloc=saslog;
LIBNAME permdata DB2 DB=MA40 SCHEMA=SASTDATA connection=global
dbcommit=0 USER=sasuser PASSWORD=xxx;
LIBNAME tempdata DB2 DB=MA40 SCHEMA=SASTDATA connection=global
dbcommit=0 dbmstemp=yes USER=sasuser PASSWORD=xxx;
proc sql;
create table tempdata.ptyacc as
(
select pty.pty_id
from permdata.pty_rb pty,
permdata.PTY_ARNG_PROD_RB acc
where acc.ACC_PD_CTGY_CD = 'LOC'
and acc.pty_id = pty.pty_id
group by pty.pty_id having count(*) > 5
);
create table tempdata.ptyacloc as
(
select ptyacc.pty_id,
acc.ACC_APPSYS_ID,
acc.ACC_CO_NO,
acc.ACCNO,
acc.ACC_SUB_NO,
acc.ACC_PD_CTGY_CD
from tempdata.ptyacc ptyacc,
perm data.PTY_ARNG_PROD_RB acc
where ptyacc.pty_id = acc.pty_id
and acc.ACC_PD_CTGY_CD = 'LOC'
);
create table tempdata.righttab as
(
select ptyacloc.pty_id
from permdata.loc_acc loc,
tempdata.ptyacloc ptyacloc
where
ptyacloc.ACC_APPSYS_ID = loc.ACC_APPSYS_ID
and ptyacloc.ACC_CO_NO = loc.ACC_CO_NO
and ptyacloc.ACCNO = loc.ACCNO
and ptyacloc.ACC_SUB_NO = loc.ACC_SUB_NO
and ptyacloc.ACC_PD_CTGY_CD = loc.ACC_PD_CTGY_CD
and loc.ACC_CURR_LINE_AM - loc.ACC_LDGR_BL > 20000
);
select * from tempdata.ptyacc
except
select * from tempdata.righttab;
drop table tempdata.ptyacc;
drop table tempdata.ptyacloc;
drop table tempdata.righttab;
quit;