Desktop productivity for business analysts and programmers

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

Reply
Occasional Contributor
Posts: 9

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

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.

Super User
Super User
Posts: 7,720

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

[ Edited ]

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. 

Occasional Contributor
Posts: 9

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

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. 

 

Respected Advisor
Posts: 3,156

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

 

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.

 

Occasional Contributor
Posts: 9

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

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.

Super User
Super User
Posts: 7,720

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

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

Occasional Contributor
Posts: 9

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

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.

 

Super User
Super User
Posts: 7,720

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

It should look something like:

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

Occasional Contributor
Posts: 9

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

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 Smiley Sad

 

Contributor SJN
Contributor
Posts: 21

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

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. Smiley HappySmiley Happy

Super User
Super User
Posts: 6,848

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

[ Edited ]

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;

 

Super User
Posts: 5,388

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

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

Data never sleeps
Occasional Contributor
Posts: 9

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

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 Smiley Sad

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

Super User
Super User
Posts: 6,848

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

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.

 

Occasional Contributor
Posts: 9

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

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. 

 

Ask a Question
Discussion stats
  • 14 replies
  • 1228 views
  • 1 like
  • 6 in conversation