Desktop productivity for business analysts and programmers

Problem while updating Oracle tables using SAS EG

Reply
Frequent Contributor
Posts: 89

Problem while updating Oracle tables using SAS EG

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

EncounterMeasureValue
1PSI21
2PSI11
3PSI31
4PSI41
1PSI51
1PSI61
1PSI71
2PSI81
2PSI91
2PSI101
11PSI111
12PSI121
13PSI131
14PSI141
15PSI151
16PSI161

Test_data_Two

EncounterMeasureValue
1PSI22
2PSI12
3PSI32
4PSI42
1PSI52
1PSI62
1PSI72
2PSI82
2PSI92
2PSI102
18PSI112
18PSI122
18PSI132
18PSI142
18PSI152
18PSI161

Test Data after update statement

1PSI22
2PSI12
3PSI32
4PSI42
1PSI52
1PSI62
1PSI72
2PSI82
2PSI92
2PSI102
11PSI111
12PSI121
13PSI131
14PSI141
15PSI151
16PSI161
18PSI112
18PSI122
18PSI142
18PSI132
18PSI161
18PSI152

Thanks

Super Contributor
Posts: 307

Re: Problem while updating Oracle tables using SAS EG

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

Super Contributor
Posts: 307

Re: Problem while updating Oracle tables using SAS EG

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

execute (commit) by oracle;

Super Contributor
Posts: 307

Re: Problem while updating Oracle tables using SAS EG

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;

Valued Guide
Posts: 3,208

Re: Problem while updating Oracle tables using SAS EG

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.     

---->-- ja karman --<-----
Frequent Contributor
Posts: 89

Re: Problem while updating Oracle tables using SAS EG

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

Ask a Question
Discussion stats
  • 5 replies
  • 430 views
  • 6 likes
  • 3 in conversation