BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASAna
Quartz | Level 8

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

12 REPLIES 12
ballardw
Super User

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.

 

SASAna
Quartz | Level 8

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,

Ksharp
Super User

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;
SASAna
Quartz | Level 8

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

 

Thanks,

Ksharp
Super User

Can you remove  "(dbkey=id)" ?

SASAna
Quartz | Level 8
Thanks for the suggestion , But Still the same error.
Ksharp
Super User

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

SASAna
Quartz | Level 8
That really worked !!! Thanks much for helping me out, learned something new today.

Thanks Again
suraj
Obsidian | Level 7

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

Ksharp
Super User

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


suraj
Obsidian | Level 7

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

Ksharp
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 2443 views
  • 1 like
  • 4 in conversation