PROC SQL - UPDATE

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 84
Accepted Solution

PROC SQL - UPDATE

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;


Accepted Solutions
Solution
‎10-20-2016 11:06 PM
Super User
Posts: 9,681

Re: PROC SQL - UPDATE

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

View solution in original post


All Replies
Super User
Posts: 10,500

Re: PROC SQL - UPDATE

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.

 

Frequent Contributor
Posts: 84

Re: PROC SQL - UPDATE

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,

Super User
Posts: 9,681

Re: PROC SQL - UPDATE

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;
Frequent Contributor
Posts: 84

Re: PROC SQL - UPDATE

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

 

Thanks,

Super User
Posts: 9,681

Re: PROC SQL - UPDATE

Can you remove  "(dbkey=id)" ?

Frequent Contributor
Posts: 84

Re: PROC SQL - UPDATE

Thanks for the suggestion , But Still the same error.
Solution
‎10-20-2016 11:06 PM
Super User
Posts: 9,681

Re: PROC SQL - UPDATE

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

Frequent Contributor
Posts: 84

Re: PROC SQL - UPDATE

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

Thanks Again
Occasional Contributor
Posts: 17

Re: PROC SQL - UPDATE

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);

Super User
Posts: 9,681

Re: PROC SQL - UPDATE


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).


Occasional Contributor
Posts: 17

Re: PROC SQL - UPDATE

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

Super User
Posts: 9,681

Re: PROC SQL - UPDATE

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 12 replies
  • 681 views
  • 0 likes
  • 4 in conversation