Specifies whether SAS creates temporary or permanent tables.
Valid in: SAS/ACCESS LIBNAME statement
Default: NO
Requirement: To specify this option, you must first specify CONNECTION=GLOBAL—except for Microsoft SQL Server, which defaults to UNIQUE.
Data source: Aster nCluster, DB2 under UNIX and PC Hosts, DB2 under z/OS, Greenplum, HP Neoview, Microsoft SQL Server, MySQL, Netezza, ODBC, OLE DB, Oracle, Sybase IQ, Teradata
See: CONNECTION= LIBNAME option, Temporary Table Support for SAS/ACCESS



Syntax Description

specifies that SAS creates one or more temporary tables.
specifies that SAS creates permanent tables.


To significantly improve performance, you must also set 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 and a permanent table, you need a libref for each table and these librefs must successfully share a global connection.
DB2 under z/OS, Oracle, and Teradata: 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.

Example: Create and Join a Permanent and a Temporary Table

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,
      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
          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
select * from tempdata.righttab;
drop table tempdata.ptyacc;
drop table tempdata.ptyacloc;
drop table tempdata.righttab;