topic Re: Merge in PROC SQL/Libname in SAS Programming

Merge in PROC SQL/Libname

SASAna — Wed, 31 Oct 2018 00:56:10 GMT

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

Re: Merge in PROC SQL/Libname

Reeza — Wed, 31 Oct 2018 01:33:59 GMT

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.

Re: Merge in PROC SQL/Libname

ChrisNZ — Wed, 31 Oct 2018 02:36:02 GMT

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.