The LIBNAME Statement for Relational Databases

DBMSTEMP= LIBNAME Option

Specifies whether SAS creates temporary or permanent tables.

Default value:	NO
Valid in:	SAS/ACCESS LIBNAME statement
DBMS support:	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

Syntax
Syntax Description
Details
Examples
See Also

Syntax

DBMSTEMP=YES | NO

Syntax Description

YES: specifies that SAS creates one or more temporary tables.
NO: specifies that SAS creates permanent tables.

Details

To specify this option, you must first specify CONNECTION=GLOBAL, except for Microsoft SQL Server, which defaults to UNIQUE. 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.

Examples

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;