topic Re: Need a Volatile Table from Tereadata to SAS EG Syntax in SAS Enterprise Guide

Need a Volatile Table from Tereadata to SAS EG Syntax

IgawaKei29 — Tue, 30 Apr 2024 15:35:33 GMT

I am having an issue where I am trying to create a volatile table using SAS, then being able to connect and review this table. Ultimately I am trying to join a Teradata Volatile table with Hadoop data, but I figure this is the first step. You can see a sample layout of my code below, and the only error I get when running is that my_VTD_Test doesn't exist, which is true at the start of the program. Everything else completes just fine, but when I try to look at the table data, I get an error that it doesn't exist again. So it looks like its running but not gathering any data. I appreciate any feedback with this.

libname tdwork teradata AUTHDOMAIN=MYAUTH 
    mode=teradata  server="myserver" 
    connection=global dbmstemp=yes;


PROC SQL NOERRORSTOP;
CONNECT TO TERADATA (AUTHDOMAIN=TDAUTH  server="myserver" connection=global mode=teradata);
  execute (drop table my_VTD_Test) BY Teradata;
  execute( create volatile table my_VTD_Test as
  		  (select Name, Place, Service

from MYTDSCHEMA.Table_NAME
		
			

 )
		with data primary index(Name) On Commit Preserve Rows ) by teradata; 
		
				DISCONNECT FROM teradata;
quit;

Re: Need a Volatile Table from Tereadata to SAS EG Syntax

Tom — Tue, 30 Apr 2024 16:40:19 GMT

First thing I would do is not make two connections to Teradata. Then you don't have to worry about whether or not SAS will make two different connections to Teradata. Just re-use the TDWORK connection you made with the LIBNAME statement in your PROC SQL code.

libname tdwork teradata AUTHDOMAIN=MYAUTH 
    mode=teradata  server="myserver" 
    connection=global dbmstemp=yes
;

PROC SQL NOERRORSTOP;
CONNECT USING TDWORK as TERADATA;
EXECUTE BY TERADATA (drop table my_VTD_Test);
EXECUTE BY TERADATA 
( create volatile table my_VTD_Test as
  (select Name, Place, Service
   from MYTDSCHEMA.Table_NAME
  ) with data primary index(Name) On Commit Preserve Rows 
);
quit;

You might also need to set the schema in your LIBNAME statement. I seem to remember that it uses your teradata username as the schema name for the volatile tables.

Re: Need a Volatile Table from Tereadata to SAS EG Syntax

IgawaKei29 — Fri, 03 May 2024 15:27:05 GMT

Thank you, this really helped!