topic Re: Loading SAS data set to a Teradata Volatile Temporary Table in SAS Programming

Loading SAS data set to a Teradata Volatile Temporary Table

mrsamtime — Thu, 13 Aug 2009 18:31:44 GMT

I am trying to load a simple SAS file with about 200 observations on it to Teradata, so I can use these as limiting criteria on a query. I don't have the ability to create tables in teradata, so the only option is to load the overstrains to a volatile table and then use that in my query.

The only help I am getting through local channels is to not use SAS and use Teradata SQL assistant. I am a big fan of SAS, and I think it should be able to support this functionality, but everything I have tried has ended in failure. Has anyone been able to do this?

Re: Loading SAS data set to a Teradata Volatile Temporary Table

LinusH — Fri, 14 Aug 2009 14:00:56 GMT

Maybe you don't have do load a Teradata table. take a look at the DBKEY= option.
http://support.sas.com/documentation/cdl/en/acreldb/61890/HTML/default/a002253008.htm

/Linus

Re: Loading SAS data set to a Teradata Volatile Temporary Table

Peter_C — Mon, 17 Aug 2009 16:36:53 GMT

through the odbc access I was able to pass an "in-list" to support a teradata query.
You might have luck with a similar approach. It turns data into syntax, but it worked for me.

good uck
PeterC Message was edited by: Peter.C

Re: Loading SAS data set to a Teradata Volatile Temporary Table

HeatherS_Highmark — Thu, 20 Aug 2009 18:07:11 GMT

libname x teradata user=&userid password=&passwd server=myTeradataServer connection=global;

/* Create a volatile table */
option debug=dbms_timers
sastrace=',,,d'
sastraceloc=saslog no$stsuffix;

proc sql;
connect to teradata(user=&userid password=&passwd server=myTeradataServer
connection=global bulkload=yes);
execute
(CREATE VOLATILE TABLE temp1 (var1 varchar(15)) ON COMMIT PRESERVE ROWS ) by
teradata; execute ( COMMIT WORK ) by teradata; INSERT INTO x.temp1
SELECT * FROM mysasdataset.var1;

quit;

/* Connect to the volatile table through the global connect.*/
rsubmit;
option debug=dbms_timers
sastrace=',,,d'
sastraceloc=saslog no$stsuffix;

proc sql;
connect to teradata (user=&userid password=&passwd server=myTeradataServer
connection=global);
select * from connection to teradata
( select b.* from a, b
where xxxxxx);
quit;

/* Volatile table is automatically dropped */

libname x clear ;