Problem while updating Oracle tables using SAS EG

[learner_sas]

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

[Fugue]

Try adding the SCHEMA= option and see if that works.

[Fugue]

Also, you may need to add the following statement before the quit statement;

execute (commit) by oracle;

[Fugue]

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;

[jakarman]

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.     

[learner_sas]

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