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!
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.
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.