10-07-2015 08:44 AM
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.
connect to teradata(user=user password=password server=servername database=databasename);
create table test_1 as
select * from connection to teradata
from temIP as IP
inner join temCUST as Cust
on IP.IP_ID = Cust.CUST_ID);
disconnect from teradata;
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.
10-07-2015 08:51 AM - edited 10-07-2015 08:52 AM
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.
10-07-2015 09:11 AM
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.
10-07-2015 08:53 AM
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.
10-07-2015 09:08 AM
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.
10-07-2015 10:09 AM
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.
connect to XYX (....);
execute by XYZ (create table temp1 (col1 int)) by teradata;
disconnect from XYZ;
Kindly help me out to solve this issue.
10-07-2015 10:16 AM
It should look something like:
connect to terradata (....);
execute (create table temp1 (col1 int)) by teradata;
disconnect from terradata;
10-07-2015 10:40 AM
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
10-07-2015 08:57 AM
We use to connect to teradata by creating ODBC connection. You can try in that way.
CONNECT TO ODBC (USER=&USR PASSWORD=&PWRD DSN=<<Data Base>>);
CREATE TABLE Status
Select * from connection to odbc
DISTINCT Aa AS A1
FROM A AS A1
Disconnect from odbc;
If you want to create odbc
Start>tye"ODBC (Select obbc32)> Add> teradata
Hope this helps your problem.
10-07-2015 01:33 PM - edited 10-07-2015 01:36 PM
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;
10-08-2015 03:37 AM
Seems like @Tom was the only one that have read the following:
10-08-2015 11:44 AM
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.
10-08-2015 01:41 PM
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.
10-12-2015 02:29 PM
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.