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?
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!
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.
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
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.