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

[Tornal]

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.

[RW9]

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. 

[Tornal]

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. 

[Haikuo]

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.

[Tornal]

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.

[RW9]

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

[Tornal]

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.

[RW9]

It should look something like:

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

[Tornal]

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 😞

[SJN]

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. :):)

[Tom]

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;

[LinusH]

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#p0gu3f7qh8i83jn18a...

[Tornal]

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.

[Tom]

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.

[Tornal]

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.