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?
The approach that will perform best:
The approach that will perform best:
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?
Would something like this work, @Patrick ?
proc append base=TD.TERADATA_TABLE (multiload=yes upsert=yes) data=WORK.SAS_TABLE; run;
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.
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.