topic Re: PROC SQL - UPDATE in SAS Programming

PROC SQL - UPDATE

SASAna — Thu, 20 Oct 2016 21:16:10 GMT

Hi All,

I am trying to update the Oracle table with SAS dataset contents with some merge conditions & queried as belwo and getting error message as ERROR 73-322: Expecting an =.

SAS dataset - MANUAL_TEMP

Oracle table -dbms.oracle_table

libname dbms oracle user=CCCC password= AAAA path= BBBB schema=xyz;

PROC SQL;
update dbms.oracle_table(dbkey=XYZ) A
SET
A.test_date = (SELECT B.test_date FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6),
A.test_upload = (SELECT B.test_upload FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6),
A.test_reason = (SELECT B.test_reason FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6),
A.test_exclusion = (SELECT A.test_exclusion || '|' || B.test_exclusion FROM MANUAL_TEMP B
WHERE
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6)
where exists (
select 1
from MANUAL_TEMP B
where
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6);

QUIT;

Re: PROC SQL - UPDATE

ballardw — Thu, 20 Oct 2016 23:34:48 GMT

Post the code from log along with the error message. It will help if you paste it into the box opened by clicking on the "run" icon at the top of you message to preserve formatting as the _ character shows exactly where the unexpected value appears.

Note that may not be the cause of the error as missing or extra semicolons or commas before that line may be the actual cause.

Re: PROC SQL - UPDATE

SASAna — Fri, 21 Oct 2016 01:50:01 GMT

Sorry, it has some PHI information to provide actual code from the log, so i just changed them to example.

update dbms.&MyTable(dbkey=XYZ) A

993 SET
994 A.temp1= (SELECT B.temp1 FROM MANUAL_TEMP B
_
73
76
ERROR 73-322: Expecting an =.

ERROR 76-322: Syntax error, statement will be ignored.

Thanks for looking into,

Re: PROC SQL - UPDATE

Ksharp — Fri, 21 Oct 2016 02:13:35 GMT

You can use the following options to see what kind of sql SAS passed to Oracle and debug it from there.

options sastrace=',,,d' sastraceloc=saslog;

Re: PROC SQL - UPDATE

SASAna — Fri, 21 Oct 2016 02:19:44 GMT

I tried adding that, but It didnt help me much to trace where it is going wrong.

Thanks,

Re: PROC SQL - UPDATE

Ksharp — Fri, 21 Oct 2016 02:31:47 GMT

Can you remove "(dbkey=id)" ?

Re: PROC SQL - UPDATE

SASAna — Fri, 21 Oct 2016 02:36:33 GMT

Thanks for the suggestion , But Still the same error.

Re: PROC SQL - UPDATE

Ksharp — Fri, 21 Oct 2016 02:46:08 GMT

I know what is going on , Remove 'A.' from the variable

993 SET
994 A.temp1= (SELECT B.temp1 FROM MANUAL_TEMP B

---->

993 SET
994 temp1= (SELECT temp1 FROM MANUAL_TEMP B

Re: PROC SQL - UPDATE

SASAna — Fri, 21 Oct 2016 03:06:31 GMT

That really worked !!! Thanks much for helping me out, learned something new today.

Thanks Again

Re: PROC SQL - UPDATE

suraj — Wed, 05 Apr 2017 14:11:12 GMT

I am using the following query to update a table . Can you please help why we are using exists here? Thanks!

where exists (
select 1
from MANUAL_TEMP B
where
A.temp1 = B.temp1
AND A.temp2 = B.temp2
AND A.temp3 = B.temp3
AND A.temp4 = B.temp4
AND A.temp5 = B.temp5
AND A.temp6 = B.temp6);

Re: PROC SQL - UPDATE

Ksharp — Wed, 05 Apr 2017 14:41:05 GMT


exist(...) will return TRUE if you can SELECT ANY obs from that condition.

WHERE  means update the obs which satisfy that condition ( exist will return TRUE).

Re: PROC SQL - UPDATE

suraj — Thu, 06 Apr 2017 09:17:44 GMT

Thanks a lot for help.. it worked for me.

I am just curious to know why we are using where again when we are already using it in subquery.

I also used it in same way and it's working for me. but just wants to clear my doubt. Thanks in advance!

Regards,

Suraj

Re: PROC SQL - UPDATE

Ksharp — Thu, 06 Apr 2017 12:34:59 GMT

I also confused before.

WHERE clause here is to update sub-dataset,

if you don't specify WHERE then SQL will update all the obs.