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

How to update a teradata table using SAS access?

CharlotteCain — Mon, 03 Apr 2017 15:59:40 GMT

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?

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

PatrickCuba — Mon, 03 Apr 2017 18:45:40 GMT

Have you tried UPDATE, MLOAD?

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

CharlotteCain — Tue, 04 Apr 2017 02:55:45 GMT

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.

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

Ksharp — Tue, 04 Apr 2017 04:18:52 GMT

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

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

LinusH — Tue, 04 Apr 2017 05:00:18 GMT

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.

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

CharlotteCain — Tue, 04 Apr 2017 17:39:05 GMT

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!