BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
KJazem
Obsidian | Level 7

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? 

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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>);

 

 

 

View solution in original post

4 REPLIES 4
Patrick
Opal | Level 21

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
Obsidian | Level 7

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
Obsidian | Level 7

Would something like this work, @Patrick ?

 

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

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.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 897 views
  • 0 likes
  • 3 in conversation