Hello everybody,
I have a table in Oracle which needed to be updated. there for I used following code,
proc sql ;
connect to oracle(user=rrimal orapw=XXXXXX path=clrabc);
execute( merge into clrabc.TEST_DATA A
USING (SELECT * FROM clrabc.tEST_DATA_TWO ) B
ON (A.ENCOUNTER= B.ENCOUNTER AND A.MEASURE=B.MEASURE )
WHEN MATCHED THEN
UPDATE SET A.VALUE = B.VALUE
WHEN NOT MATCHED THEN
INSERT (A.ENCOUNTER,A.MEASURE,A.VALUE)
VALUES (B.ENCOUNTER,B.MEASURE,B.VALUE) by oracle;
disconnect from oracle;
quit;
But it did not update table test_data. If I run same code inside Oracle it works smoothly. I am wondering if there is a way to fix this issue.
following are the demo table
Test_data
Encounter | Measure | Value |
1 | PSI2 | 1 |
2 | PSI1 | 1 |
3 | PSI3 | 1 |
4 | PSI4 | 1 |
1 | PSI5 | 1 |
1 | PSI6 | 1 |
1 | PSI7 | 1 |
2 | PSI8 | 1 |
2 | PSI9 | 1 |
2 | PSI10 | 1 |
11 | PSI11 | 1 |
12 | PSI12 | 1 |
13 | PSI13 | 1 |
14 | PSI14 | 1 |
15 | PSI15 | 1 |
16 | PSI16 | 1 |
Test_data_Two
Encounter | Measure | Value |
1 | PSI2 | 2 |
2 | PSI1 | 2 |
3 | PSI3 | 2 |
4 | PSI4 | 2 |
1 | PSI5 | 2 |
1 | PSI6 | 2 |
1 | PSI7 | 2 |
2 | PSI8 | 2 |
2 | PSI9 | 2 |
2 | PSI10 | 2 |
18 | PSI11 | 2 |
18 | PSI12 | 2 |
18 | PSI13 | 2 |
18 | PSI14 | 2 |
18 | PSI15 | 2 |
18 | PSI16 | 1 |
Test Data after update statement
1 | PSI2 | 2 |
2 | PSI1 | 2 |
3 | PSI3 | 2 |
4 | PSI4 | 2 |
1 | PSI5 | 2 |
1 | PSI6 | 2 |
1 | PSI7 | 2 |
2 | PSI8 | 2 |
2 | PSI9 | 2 |
2 | PSI10 | 2 |
11 | PSI11 | 1 |
12 | PSI12 | 1 |
13 | PSI13 | 1 |
14 | PSI14 | 1 |
15 | PSI15 | 1 |
16 | PSI16 | 1 |
18 | PSI11 | 2 |
18 | PSI12 | 2 |
18 | PSI14 | 2 |
18 | PSI13 | 2 |
18 | PSI16 | 1 |
18 | PSI15 | 2 |
Thanks
Try adding the SCHEMA= option and see if that works.
Also, you may need to add the following statement before the quit statement;
execute (commit) by oracle;
Just took another look at this and noticed that you also missing a closing parenthesis. As per my previous note, you may also need to add the SCHEMA= option. Try the following . . . .
proc sql ;
connect to oracle(user=rrimal orapw=XXXXXX path=clrabc);
execute( merge into clrabc.TEST_DATA A
USING (SELECT * FROM clrabc.tEST_DATA_TWO ) B
ON (A.ENCOUNTER= B.ENCOUNTER AND A.MEASURE=B.MEASURE )
WHEN MATCHED THEN
UPDATE SET A.VALUE = B.VALUE
WHEN NOT MATCHED THEN
INSERT (A.ENCOUNTER,A.MEASURE,A.VALUE)
VALUES (B.ENCOUNTER,B.MEASURE,B.VALUE)
);
execute (commit) by oracle;
disconnect from oracle;
quit;
SAS/ACCESS(R) 9.4 for Relational Databases: Reference, Second Edition (SQL Pass-Through Facility Specifics for Oracle)
You are coding explicit pass through. That means in the execute block there is no difference to the Oracle SQL it is given that way to oracle (no need for schema). Using the libname it can generate implicit pass through, SAS transforms the Ansi-SQL to Oracle-SQL.
This result can be traced by options sqltrace ..
The libname to oracle usage is needing a schema because that makes it equivalent to the SAS-librarie. A schema within a DBMS is referring to a number of tables. The path is another definition in this dbms connection game. it is the name of finding then DBMS (TNS-names) wiht some default settings. Sometimes for the ease of remembering them the Tnsname entry can be given the same name as the schema. But you have to remember they are different type of objects.
As the clrabc tekst is coded different (lowcase) it looks te be changed by hand.
Within the execute block it must must exactly the same as the code that has run directly in Oracle
The path statement must be the same and using the same TNSNAMES file setting with a fucntional equal Oracle client. It is possible to give the TNSnames content into the path as value.
A common pitfall is thinking the schema.table_a (oracel explicit) is the same as saslibname.tabel_a (SAS datasets). As it is often being set up as seeming equal.
Thank you . Jaap Karman and Fugue. From both of your suggestions. I wrote following quotes and it worked. To make sure,I ran code in ORACLE and just paste on execute part.
proc sql ;
connect to oracle(user=XYZ orapw=XYZ path=DEVE);
execute( merge into TEST_DATA A
USING (SELECT * FROM TEST_DATA_TWO ) B
ON (A.ENCOUNTER= B.ENCOUNTER AND A.MEASURE=B.MEASURE )
WHEN MATCHED THEN
UPDATE SET A.VALUE = B.VALUE
WHEN NOT MATCHED THEN
INSERT (A.ENCOUNTER,A.MEASURE,A.VALUE)
VALUES (B.ENCOUNTER,B.MEASURE,B.VALUE) )
by oracle;
disconnect from oracle;
quit;
Thank you
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: