BookmarkSubscribeRSS Feed
Tornal
Calcite | Level 5

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.

14 REPLIES 14
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Calcite | Level 5

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
Onyx | Level 15

 

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
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

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

Tornal
Calcite | Level 5

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
Diamond | Level 26 RW9
Diamond | Level 26

It should look something like:

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

Tornal
Calcite | Level 5

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
Fluorite | Level 6 SJN
Fluorite | Level 6

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
Super User Tom
Super User

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;

 

Tornal
Calcite | Level 5

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
Super User Tom
Super User

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
Calcite | Level 5

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. 

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 14 replies
  • 7171 views
  • 1 like
  • 6 in conversation