topic Re: SAS/Access to Teradata - How to create volatile tables from SAS datasets in SAS Enterprise Guide

SAS/Access to Teradata - How to create volatile tables from SAS datasets

Sid — Thu, 26 Feb 2009 00:22:12 GMT

Hi,

I want to create volatile table in teradata using SAS dataset.
I tried varios options but still stuck!
SAS support documentation gives information only about creating Volatile tables and not for creating volatile tables using SAS datasets!

1> Does anyone know how this can be done by Libname? What options do i need to specify?
2> By Pass-thru, I tried this =
proc sql;

CONNECT to teradata(user='XXX' pw='YYY' server=XX connection=global);
execute (CREATE VOLATILE TABLE temp1 (
UNIQUE_ID char(10) NOT NULL
)
ON COMMIT PRESERVE ROWS) by teradata;

quit;

But after this, Insert does not work. Pass Thru Insert requires only Literal values.

I also tried =>

Create volatile table as (select * from sas_dataset)
But this also does not work. I am running SAS on production server (& not on my local machine). So, SAS does not recognize sas_dataset inside CONNECT statement !

I would be very much thankful for suggestions ??!!

Re: SAS/Access to Teradata - How to create volatile tables from SAS datasets

sbb — Thu, 26 Feb 2009 03:05:42 GMT

You may find information at the SAS support website http://support.sas.com/ and do a search on your topic, or as I did, you will find the Google advanced search argument will generate some DOC results found at the SAS site:

create volatile table teradata site:sas.com

Scott Barry
SBBWorks, Inc.

Re: SAS/Access to Teradata - How to create volatile tables from SAS datasets

Sid — Thu, 26 Feb 2009 16:15:47 GMT

Thanks a lot!

Proc Append is working absolutely fine!

Apprecite your help.

Re: SAS/Access to Teradata - How to create volatile tables from SAS datasets

deleted_user — Tue, 03 Mar 2009 18:14:45 GMT

If you want to setup a volatile table then insert from a SAS table use below.

Please note that the volatile table only reliably lasts for the duration of the entire Proc Sql.

code:

libname trlib clear;
libname trlib teradata user=youruserid password="yourpassword" connection=global;

/* Setup the volatile table with the variables.*/

proc sql;
connect to teradata(User=youruserid password="yourpassword" connection=global);
execute (create volatile table temp1
( first_variable decimal (12,0), second_variable decimal(13))
on commit preserve rows) by teradata;
execute (commit work) by teradata;

insert into trlib.temp1
select first_variable , second_variable
from YOUR_SAS_TABLE ;

.... other SAS and Teradata retrievals

quit;