Update records in Teradata from SAS

[KJazem]

I want to update table records in Teradata from SAS, if a column from another table is different/changed. 

The table in teradata has an id, product, record_id, and updated_at as columns. Every day, a new table (similar columns as the TD table) is generated in SAS, and if the product changes (from yesterday's data which is in TD), the product, record_id, and updated_at columns in Teradata are updated.

If I want to update a record manually, I can just do an update command and set the new column where a condition is met. 

At the same time, if a new id is generated in SAS, I want to append it to the table in TD. Is there a way to achieve this in one command - updating existing records and inserting new ones? 

[Patrick · Accepted solution]

The approach that will perform best:

1. Load your SAS table into Teradata (ideally a Teradata temporary table)
2. Write Teradata SQL using a Teradata MERGE statement https://docs.teradata.com/r/huc7AEHyHSROUkrYABqNIg/X0jsZHHHpAOpP_j4XekrkQ 
   
   1. And then copy/paste this Teradata SQL into an explicit Proc SQL pass through block ( EXECUTE(<Teradata SQL with the MERGE>);

[KJazem]

Thanks for this.

Only issue is having to write the SAS table in Teradata every time, which can sometimes be very slow for me. Is there a lesser optimized version that updates and/or inserts using SAS syntax?

EDIT: for the MERGE INTO syntax, would it look like this inside the execute command? Assume T is the table in Teradata to be updated/inserted into and S the table from SAS (temp) pushed to Teradata.

merge into T
using (select id, product, updated_at from S) as source (a, b, c)
on T.id = a
when matched then
  update set product = b, updated_at = c
when not matched then
  insert (a, b, c)

This is will update all ids regardless if product doesn't change, just 'force' update all records then insert new instances? 

[KJazem]

Would something like this work, @Patrick ?

proc append base=TD.TERADATA_TABLE (multiload=yes upsert=yes)  data=WORK.SAS_TABLE;
run;

[SASKiwi]

PROC APPEND will work if you delete all of the records to be updated first, then apply them as an INSERT, which is what APPEND does. The problem is how will you identify the rows to be deleted? If you need a Teradata temporary table to identify the rows to delete, then you are not really any better off.