topic Problem while updating Oracle tables using SAS EG in SAS Enterprise Guide

Problem while updating Oracle tables using SAS EG

learner_sas — Fri, 16 Aug 2013 20:41:28 GMT

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

Re: Problem while updating Oracle tables using SAS EG

Fugue — Fri, 16 Aug 2013 21:09:00 GMT

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

Re: Problem while updating Oracle tables using SAS EG

Fugue — Fri, 16 Aug 2013 21:12:57 GMT

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

execute (commit) by oracle;

Re: Problem while updating Oracle tables using SAS EG

Fugue — Fri, 16 Aug 2013 21:47:06 GMT

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;

Re: Problem while updating Oracle tables using SAS EG

jakarman — Sat, 17 Aug 2013 06:45:19 GMT

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.

Re: Problem while updating Oracle tables using SAS EG

learner_sas — Sun, 18 Aug 2013 12:23:48 GMT

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