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;
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
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.
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,
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;
I tried adding that, but It didnt help me much to trace where it is going wrong.
Thanks,
Can you remove "(dbkey=id)" ?
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
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);
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).
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
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.
Registration is now open for SAS Innovate 2025 , our biggest and most exciting global event of the year! Join us in Orlando, FL, May 6-9.
Sign up by Dec. 31 to get the 2024 rate of just $495.
Register now!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.