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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.