Update Oracle table from SAS dataset

0
votes

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.

2
sqloraclesasoracle-pro-c
Though you can do it using Sql pass through in SAS, Why can't you do the update in Oracle itself? Also, please mention what you've tried till now along with the code. – Praneeth Rachumallu
It is a part of a SAS webservice which is called in SOAP request. So basically it is a part of an automated process in the background when the users call the procedure/webservice. So all in all I can't just call it all the time in Oracle manually. I tried proc sql delete all the rows from the Oracle table and then insert into all the observations from the dataset into the "truncated" table in oracle. The modified observations usually are 5 - 10% of all the rows, so I delete and insert ~~90% of the observations for no reason. – Martin
It is working fairly quickly, I was just thinking if there's a more efficient way. – Martin
Can't you upload the transaction data to a temporary table oracle, then use explicit pass through to update the main table? – Quentin
@Quentin thanks for the suggestion, but it's a strict schema in which I can't create or drop tables. – Martin

2 Answers

0
votes

Surely there are UPDATE and INSERT methods in proc SQL. Also, check if SAS will allow you to do other SQL operations "execute immediate" (such as PL/SQL will allow) where you can construct the SQL statement as a string, then send it to Oracle to execute.

0
votes

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.