BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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?

1 ACCEPTED SOLUTION

Accepted Solutions
CharlotteCain
Quartz | Level 8

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

5 REPLIES 5
PatrickCuba
Obsidian | Level 7
Have you tried UPDATE, MLOAD?
CharlotteCain
Quartz | Level 8

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. 

Ksharp
Super User

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

 

CharlotteCain
Quartz | Level 8

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!

LinusH
Tourmaline | Level 20
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

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 5 replies
  • 2742 views
  • 2 likes
  • 4 in conversation