How do I update an Oracle table in SAS from a SAS dataset?

Here's the scenario:

1. Trough a libname I load an Oracle table into a SAS dataset.

2. Make some data processing during which I UPDATE some values, INSERT some new observations and DELETE some observations in the dataset.

3. I need to update the original Oracle table with the dataset I've modified in the previous step - so when there's a match between the keys of the oracle table and the dataset, then the values will be updated, when there's a missing key in the oracle table, then it will be inserted, and when there's a key which is in the Oracle table but already deleted from the dataset, then it will be deleted from the Oracle table.

NOTE: I can not create a new table in Oracle. I need to make the "updating" on the original table.

I was trying to do it in two step using MERGE INTO and DELETE, but there's no MERGE INTO in PROC SQL. I would really appreciate any help.

EDIT: I was also thinking about just truncating the oracle table and inserting the rows (talking about 4-5000 rows per procedure run), but seems like there's no built in truncate statement in PROC SQL.

Please try using the below,

Method 1:

PROC SQL;
insert into <User_Defined_Oracle_table>
select variables
from <SAS_Tables>;
QUIT;

Above creates a table that resides in the same database and schema.

  PROC SQL;
    connect to oracle (user= oraclepwd=);
    execute(
    UPDATE <Oracle_table> a SET <Column to be updated> = (SELECT <Columns to update seperated by commas>
    FROM <SAS_table> b
    WHERE a.<VARIABLE>=b.<VARIABLE>)
    WHERE exists (select * from <SAS_table> b
    WHERE a.<VARIABLE>=b.<VARIABLE> ))
    by oracle;
    QUIT; 
    
    PROC SQL;
    connect to oracle
    (user= oraclepwd=};
    execute (truncate table <SAS_table>) by
    oracle;
    QUIT; 

This is one of the efficient ways to update the oracle table. Please refer to Update Oracle using SAS for more information.

Method 2:

LIBNAME Sample oracle user=  password= path=  schema= ; run;
PROC SQL;
UPDATE Sample_Oracle.<Table_Name> as a SET <Variable_Name> = (SELECT <Varibales>
FROM <Sas_table> as b
WHERE <A.Variable_Name>=<B.Variable_Name>)
WHERE exists
(select * from <Sas_table> as b
WHERE <A.Variable_Name>=<B.Variable_Name>);
QUIT; 

This method takes longer processing time of all methods.

Also,

Method 3:

%MACRO update_oracle (SAS_Table,Oracle_Table); 

Proc sql ;
select count(*) into: Count_Obs from <SAS_Table> ; Quit;

%do i = 1 %to &Count_Obs;

Proc sql; 
select <variables to update seperated by commas> into: <macros> ; Quit;

PROC SQL;
UPDATE &Oracle_Table as a
SET <Oracle_Variable_to_Update>=<Variable_macro_created_above>
WHERE <A.Variable_Name>=<B.Variable_Name> 
QUIT;

%end;
%MEND update_oracle;
%update_oracle(); 

The macro variables SAS_Table and Oracle_Table represent the SAS Dataset that contains the records to update and records to be updated in oracle, respectively.

Method 3 uses less processing time than method 2 but not as efficient as method 1.