proc SQL update? 1

[RinaGreen]

Hello everybody!

I have two tables:


1st table name is substFormat: with two num fields: Policy and new1

2nd table name is Full: with Policy field


I need to replace Policy field in FULL table with NEW1 if substFormat.policy=Full.policy

I tried to do it in Proc SQL UPDATE STATEMENT

proc sql;
update table Full
set k.policy = l.new1
from full k, substFormat l
where k.policy=l.policy
and l.policy <>.;
quit;

BUT I failed as log mentioned invalid syntax
-
79
76
ERROR 79-322: Expecting a SET.

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


What I am doing wrong?

Thank you for your time and consideration

Rina

 

[ChrisW75]

I think the first problem there is the keyword TABLE is not needed.
Try this instead, I tested this through and it works. I've been doing some similar stuff the last few weeks here updating ORacle tables.

proc sql;
  update Full  k
  set policy = (select l.new1 from substFormat l
                  where k.policy=l.policy
                  and l.policy <>.)
  where policy in(select policy from substformat)
  ;
quit;

If you do this without the second WHERE clause, it'll overwrite POLICY with blanks if they aren't in the substFormat table.
Also, be careful as FULL is a reserved word in SQL (as in FULL JOIN). I would recommend naming your table something different if possible.
Also "<>." would be more properly written as "IS NOT NULL".

 

[RinaGreen]

Chris,

Thank you for the quick and helpful responce. It works!!! I wonder if the same thing can be implemented in SAS syntax?

Thank a million.

Rina

 

[ChrisW75]

Yes it can. Use a merge to join the two tables, then do the update. You'll need to use the in= dataset options. I'll leave that as an exercise for you.

 

[6656]

I attempted to update a Sybase table (v12.5) with a SASv8.2 dataset and got a message as following:
"ERROR: MEMBER level locking for UPDATE or RANDOM access is not supported by this engine.
ERROR: PROC SQL could not undo this statement if an ERROR were to happen as it could not obtain exclusive access to the data set. This statement will not execute as the SQL
option UNDO_POLICY=REQUIRED is in effect."

The Proc SQL syntax used was same as Chris's sample code. Either database= or dblink= option were used in LIBNAME option. I accessed the DB as dbo.
Any idea to resolve the issue?

Thanks in advance!

Mike

 

[ChrisW75]

Well, I think the error message says it all really:-

"ERROR: MEMBER level locking for UPDATE or RANDOM access is not supported by this engine."

In order to run an update statement you have to lock the table, and apparently SAS can't lock a SYBASE table.

Check this out on SAS Support, it sounds like it's a bug that can be worked around:-

http://support.sas.com/techsup/unotes/SN/010/010317.html

Personally, my first port of call for any unexpected error messages it SAS Support, you can stick the error message in to the search box and be pretty confident of getting a decent result.