DATA Step, Macro, Functions and more

How to update a teradata table using SAS access?

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 137
Accepted Solution

How to update a teradata table using SAS access?

How to update a teradata table using SAS access?

 

I have two teradata tables:

Table 1:

Id   Col1    Col2

1     x          y

1     a        b

2     x          y

3    g          h

4    x          y

4    a         b

 

Table 2:

ID   Remark    Date

1     complete   02jan2004

1     pending     06jan2004

2     complete    08jan2005

3    pending      08feb2005

3    pending      07mar2005

3   pending      09mar2005

 

I neeed the remark column with the max of date to be updated to table 1. Suppose if those were sas datasets, the code is pretty easy:

 

proc sql;

select a.*, b.remark

from table1 a

left join 

(select distinct id, remark, max(date) as max_date from table2

group by id;

having date=max_date) b

on a.id=b.id;

quit;

 

But I my tables are teradate tables, I find the SAS access execute statement pretty challenging. Can anybody help?


Accepted Solutions
Solution
‎04-04-2017 01:39 PM
Frequent Contributor
Posts: 137

Re: How to update a teradata table using SAS access?

Thank you Xia,  I hope you are keeping well.

OR you could try PASS-THROUGH sql, but you need to know TD 's sql.

proc sql;

connect to teradata.......

execute (......)

by teradata

 

That's the understanding I needed. A TD sql in the execute did the job. I really appreciate it. Take care!

View solution in original post


All Replies
Occasional Contributor
Posts: 14

Re: How to update a teradata table using SAS access?

Posted in reply to CharlotteCain
Have you tried UPDATE, MLOAD?
Frequent Contributor
Posts: 137

Re: How to update a teradata table using SAS access?

Posted in reply to PatrickCuba

I didn't try MLOAD. I have been trying UPDATE statement yes. I wonder whether it is possible to execute a teradata query using proc sql access. If yes, I just don't know how. I am unable to find some examples online. 

Super User
Posts: 10,023

Re: How to update a teradata table using SAS access?

Posted in reply to CharlotteCain

Hi Charlotte,

It is not called UPDATE table, it is more like JOIN table.

 

Once you connected with TD you can manipulate it as sas table.

 

libname x tera .................

 

 

proc sql;

CREATE TABLE X.NEW_TABLE AS

select a.*, b.remark

from X.table1 a

left join 

(select distinct id, remark from X.table2

group by id;

having date=max(date)) b

on a.id=b.id;

quit;

 

 

OR you could try PASS-THROUGH sql, but you need to know TD 's sql.

proc sql;

connect to teradata.......

execute (......)

by teradata

 

Solution
‎04-04-2017 01:39 PM
Frequent Contributor
Posts: 137

Re: How to update a teradata table using SAS access?

Thank you Xia,  I hope you are keeping well.

OR you could try PASS-THROUGH sql, but you need to know TD 's sql.

proc sql;

connect to teradata.......

execute (......)

by teradata

 

That's the understanding I needed. A TD sql in the execute did the job. I really appreciate it. Take care!

Super User
Posts: 5,424

Re: How to update a teradata table using SAS access?

Posted in reply to CharlotteCain
Like @Ksharp I'm confused when you talk about update when your SQL is a query.
Either way, it looks pretty straightforward so there's a chance that this will be an implicit pass through.
You could try perhaps on smaller tables first like in a test environment or so.
Add:
Options msglevel = I sastrace = ',,,d' SASTRACELOC = saslog nostsuffix;
which will reveal how the ACCESS engine will deal with the query.
Data never sleeps
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 316 views
  • 2 likes
  • 4 in conversation