Create volatile table in Tera via SAS and view the table in SAS

[Ronein]

Hello

I want to write a code in SAS that create volatile table and then view the table in SAS .

Here are 2 examples:

Example 1 is working 100%

Example 2 is getting an error(ERROR: File TDWORK.t_VBM374.DATA does not exist)

Can explain and fix the code in order that example 2 work well?

/***Example1****/
/***Example1****/
/***Example1****/
/* Set global connection for all tables*/
libname tdwork teradata mode=teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL  dbmstemp=yes ;
/*Run in SAS-Create a volatile table*/
proc sql;
   connect to teradata(server=dwprod schema=DWP_vall authdomain=TeraDataAuth connection=global);

   execute (CREATE VOLATILE TABLE ttt (col1 INT) 
            ON COMMIT PRESERVE ROWS) by teradata;

quit;
/*Run in SAS-Insert 1 row into the volatile table*/
proc sql;
   connect to teradata(server=dwprod schema=DWP_vall authdomain=TeraDataAuth connection=global);
   execute (INSERT INTO ttt VALUES(2)) by teradata;
quit;
/* Access the temporary table through the global libref*/
proc print data=tdwork.ttt;run;


/***Example2****/
/***Example2****/
/***Example2****/
/* Set global connection for all tables*/
libname tdwork teradata mode=teradata server=dwprod schema=DWP_vall authdomain=TeraDataAuth CONNECTION=GLOBAL dbmstemp=yes ;
/*Run in SAS-Create a volatile table*/
proc sql;
connect to teradata(server=dwprod schema=DWP_vall authdomain=TeraDataAuth connection=global);
execute (
create volatile table t_VBM374 as (
SELECT top 5 *
from VBM374_USED_BRANCH_CUSTOMER
) by teradata;
quit;
/*Access the temporary table through the global libref*/
proc print data=tdwork.t_VBM374;run;
/*ERROR: File TDWORK.t_VBM374.DATA does not exist.*/

[rudfaden]

Would guess that you need to register the table in SAS meta data.

[Ronein]

What does it mean? can you show code please?

What is the difference between example 1 and example 2 that in example 1 I can view the table in SAS and in example 2 cannot? 

[Tom]

---

@rudfaden wrote:
Would guess that you need to register the table in SAS meta data.

---

No.  

I suspect you are thinking about controlled environments using SAS metadata manager.

These examples have nothing to do with that system. 

They are just using plain old SAS code.

[Tom]

Can you highlight exactly what you changed between the two examples?

To me it looks like you made these changes.

1) Different dataset (aka "table") name.

2) Used a query to define the table instead of actually defining the variables.  Did you check with Teradata query that this step actually worked.

3) In the first the dataset name is all lowercase letters, in the second it is mixed. I don't think that matters in Teradata, but it does matter in other database languages like POSTGRESQL. 

4) Your first used the clause "ON COMMIT PRESERVE ROWS" and the second did not.  Does that mean the insertion of data was never done?

At a minimum add a step to check that the volatile table creation in the second query actually worked.

select * from connection to teradata
(select cast(count(*) as float) as nobs from t_VMB374)
;

Also why are you bothering to redefine the connection each time you want to use PROC SQL?  Just reuse the connection you made with the LIBNAME statement.

proc sql;
connect using TDWORK as TERADATA.
execute by teradata (....) ;
select ... from connection to teradata (....);

[Patrick]

Inspect in detail this docu sample: Example: Create and Join a Permanent Table and a Temporary Table

DO NOT create a new connection for explicit passthrough...

...but either use implicit pass-through like in the sample code (with the libref for temp tables) OR use the libref for temp tables in your connection statement (I believe with explicit pass-through you can actually use either of the two librefs).

For your 2nd code sample: I would expect that you need to use a two level name <schema>.<table> for this to work. 

You should by now also know that just stating