SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
SASAna
Quartz | Level 8

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

2 REPLIES 2
Reeza
Super User

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. 

ChrisNZ
Tourmaline | Level 20

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.

 

 

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 825 views
  • 0 likes
  • 3 in conversation