Temporary Table Support for HP Neoview

See Temporary Table Support for SAS/ACCESS for general information about this feature.

To make full use of temporary tables, the CONNECTION=GLOBAL connection option is necessary. This option lets you use a single connection across SAS DATA steps and SAS procedure boundaries. This connection can also be shared between LIBNAME statements and the SQL pass-through facility. Because a temporary table exists only within a single connection, you need to be able to share this single connection among all steps that reference the temporary table. The temporary table cannot be referenced from any other connection.

You can currently use only a PROC SQL statement to create a temporary table. To use both the SQL pass-through facility and librefs to reference a temporary table, you must specify a LIBNAME statement before the PROC SQL step. A global connection then persists across SAS steps and even across multiple PROC SQL steps. Here is an example:

proc sql;
   connect to neoview (dsn=NDCS1_DataSource
       user=myuser password=mypwd connection=global);
   execute (create volatile table temptab1 
       as select * from permtable ) by neoview;
quit;

At this point, you can refer to the temporary table by using either the Temp libref or the CONNECTION=GLOBAL option with a PROC SQL step.

You can drop a temporary table at any time or allow it to be implicitly dropped when the connection is terminated. Temporary tables do not persist beyond the scope of a single connection.

These assumptions apply to the examples in this section.

These examples use these librefs and temporary tables.

libname saslib base 'SAS-library';
libname dept neoview dsn=Users_DataSource 
    user=myuser pwd=mypwd connection=global;

proc sql;
   connect to neoview (dsn=Users_DataSource 
      user=myuser pwd=mypwd connection=global);
   execute (create volatile table temptab1 
      (dname char(20), deptno int)) by neoview;
quit;

This first example shows how to use a heterogeneous join with a temporary table. It performs a homogeneous join on the DBMS instead of reading the DBMS table into SAS to perform the join. By using the table that was created previously, you can copy SAS data into the temporary table to perform the join.

proc sql;
   connect to neoview (dsn=Users_DataSource 
      user=myuser pwd=mypwd connection=global);
   insert into dept.temptab1 select * from saslib.joindata;
   select * from dept.deptinfo info, dept.temptab1 tab
        where info.deptno = tab.deptno;
   /* remove the rows for the next example */
   execute (delete from temptab1) by neoview;
   quit;

In this example, transaction processing on the DBMS occurs by using a temporary table instead of either DBKEY= or MULTI_DATASRC_OPT=IN_CLAUSE with a SAS data set as the transaction table.

proc sql;
connect to neoview (dsn=Users_DataSource 
   user=myuser pwd=mypwd connection=global);
   insert into dept.temptab1 select * from saslib.transdat;
   execute (update deptinfo d set dname = (select dname from temptab1)
            where d.deptno = (select deptno from temptab1)) by neoview;
quit;