Hello, is there a proc sql alternative of Oracle Merge Statement?
http://www.oracletutorial.com/oracle-basics/oracle-merge/
I'm trying to update Oracle table using value in SAS dataset within proc sql. There are multiple values to be updated . As of now, the query looks like below.
Update oracle_tbl
set value_1 = (select value_1 from sas_dataset where id = 1) ,
value_2 = (select value_2 from sas_dataset where id = 1)
where exists( ) and other filters.
I was wondering if there is a way to speed up performance. Please suggest if any. Thanks
You'll get the fastest performance, either with:
1) SQL Passthrough since the code is literally just transferred to the server - and would have to be Oracle SQL anyways
2) Can you describe some more? There may be a faster SAS way to update this.
A few considerations:
The fastest is to upload your update table to Oracle into a temp space, and run the update query there using pass-through.
If that's not possible, the next fastest is to delete the old rows, and append the new ones.
Updating one row at a time will always be slow.
Look at the options for the Oracle engine. In this case, the bulkload options and the updatebuff option are probably the ones that matter most.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: