topic Re: SAS/Access to Teradata --How to create Temporary tables in Teradata in SAS Enterprise Guide

SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Wed, 07 Oct 2015 12:44:23 GMT

Hi All,

I tried to create a temporary table in teradata but coulnd't succeed.

I want to create a temporary table in teradata for the result of the proc sql query in SAS EG.

Proc sql;
connect to teradata(user=user password=password server=servername database=databasename);
create table test_1 as
select * from connection to teradata
(select Cust.UNIQUE_ID,IP.IP_NAME
from temIP as IP
inner join temCUST as Cust
on IP.IP_ID = Cust.CUST_ID);
disconnect from teradata;

quit;

In the above example i created a sas dataset test_1 which holds the results of the sql query.
Instead of sas dataset I want to create a temporary table in teradata and use the same in other sql queries.
how can I create it?
Your suggestions would be of great help.Thank You.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

RW9 — Wed, 07 Oct 2015 12:52:25 GMT

Well, several things. Firstly good code formatting helps readability. Secondly, you would be better off doing database tasks on the data rather than from here. However if your really stuck on this:

proc sql;
  connect to XYX (....);
  execute by XYZ (create table SCHEMA.TABLE as select * from WORK.TMP);
  disconnect from XYZ;
quit;

Note, schema and table need changing to your setup.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Haikuo — Wed, 07 Oct 2015 12:53:52 GMT

Before getting into technical details, let me just ask: Do you have 'write' permission to Teradata database? If yes, then it shouldn't be a problem. If not, maybe a view will work for you.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

SJN — Wed, 07 Oct 2015 12:57:18 GMT

We use to connect to teradata by creating ODBC connection. You can try in that way.

PROC SQL;
CONNECT TO ODBC (USER=&USR PASSWORD=&PWRD DSN=<<Data Base>>);
CREATE TABLE Status
AS
Select * from connection to odbc
(SELECT
DISTINCT Aa AS A1
,A1AS A1
FROM A AS A1
);
Disconnect from odbc;
QUIT;

If you want to create odbc

Start>tye"ODBC (Select obbc32)> Add> teradata

Hope this helps your problem. :):)

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Wed, 07 Oct 2015 13:08:26 GMT

Yes I do have "write" permission to teradata database.I tried to create a table directly in Teradata SQL Assistant it worked.But When I am trying to do the same from SAS EG it's not working.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Wed, 07 Oct 2015 13:11:21 GMT

Thank you for your response.Sorry I was in a hurry so I din't format it.

I tried to create a table using the format you have mentioned but its giving me syntax error.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

RW9 — Wed, 07 Oct 2015 13:55:02 GMT

Well, you might have access, but does your EG install. I would check that. As for my post, what was the syntax error?

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Wed, 07 Oct 2015 14:09:24 GMT

Please find the syntax error below.Also I tried to add open and close paranthesis after "as" and select statement but still it gives same error.

ERROR: Teradata execute: Syntax error, expected something like a name or a Unicode delimited identifier or '(' between the 'as'
keyword and the 'select' keyword.

I just tried to create a temporary table using the below statement .It runs fine without any error but I don't see the temporary table in teradata.

proc sql;
connect to XYX (....);
execute by XYZ (create table temp1 (col1 int)) by teradata;
disconnect from XYZ;
quit;

Kindly help me out to solve this issue.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

RW9 — Wed, 07 Oct 2015 14:16:57 GMT

It should look something like:

proc sql;
connect to terradata (....);
execute (create table temp1 (col1 int)) by teradata;
disconnect from terradata;
quit;

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Wed, 07 Oct 2015 14:40:30 GMT

I executed it the same way as mentioned by you.It runs fine without errors but I am not able to see the table in the database 😞

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tom — Wed, 07 Oct 2015 17:36:59 GMT

In Teradata code you are asking to create a VOLATILE table. If you want them to persist across steps then use CONNECTION=GLOBAL option on your connections and create a LIBREF that stays defined for as long as you want your session to remain the same.

So one way to use this is create LIBREF that points to Teradata and uses the temporary (spool) space. This will let you see the volatile tables that you have created.

For example if TEMPIP is a list of id numbers that you want to upload from SAS and then use to merge back with master data in the Teradata Database then your program might look like this.

libname tdwork teradata username=&username password=&password server=&server 
  connection=global dbmstemp=yes
;
data tdwork.TEMPIP;
   ip_id=1;
   ip_name='My Name';
run;

proc sql noprint;
   connect to teradata (username=&username password=&password server=&server connection=global);
create table test_1 as
select * from connection to teradata
(select Cust.UNIQUE_ID,IP.IP_NAME
from TEMPIP as IP
inner join DATABASE_NAME.MASTER_CUST as Cust
on IP.IP_ID = Cust.CUST_ID
 );
quit;

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

LinusH — Thu, 08 Oct 2015 07:37:41 GMT

Seems like @Tom was the only one that have read the following:

http://support.sas.com/documentation/cdl/en/acreldb/68028/HTML/default/viewer.htm#p0gu3f7qh8i83jn18af4i1bf2xlp.htm

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Thu, 08 Oct 2015 15:44:48 GMT

Thanks Tom for very detailed explaination.I am able to create the table and join it back but still i don't see the table in the database 😞

I verified the existence of the table by running proc print statement.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tom — Thu, 08 Oct 2015 17:41:22 GMT

If you make new connection to Teradata you will have a different spool space and so a different set of temporary tables. If you want SAS to keep using the same connection then you need to use the CONNECTION=GLOBAL option on all connections and be consistent in your settings for username, password, server, mode, database etc.

Re: SAS/Access to Teradata --How to create Temporary tables in Teradata

Tornal — Mon, 12 Oct 2015 18:29:25 GMT

Tom,

After including the SQL_FUNCTIONS=ALL in my libname statement I was able to view the tables that I was creating in Teradata through SAS. I have read about this option in SAS documents but I am not sure whether it is safe to use this statement or not.

Kindly provide your inputs on this.