Update a variable using a substraction of two different datasets

[Cristiansg]

Hi all,

I'm trying to update a column from table a with the result of the substraction of another colum from table a minus a column from table b, under some conditions.

This is similar to what I have:

proc sql;
Update OPS

set
LEG_2 = ((select sumofdata from OPS) - (select legshares from NPT )),
LEG_1_MINUS_LEG_2_SHARES=0
where exists
(select * from OPS where
NPT .AliasissueID = OPS.aliasissueid and
NPT .ACCOUNTID = OPS.ACCOUNTID and
NPT .legshares > 0 and
OPS.ASSETMINORCLSSDESC = 'CLASS_I' and
NPT .Spread= 'Yes');
quit;

sumofdata, legshares  and LEG_2  are numeric.

The first two where conditions are just to do the join, and the others 3 are related to the process. I think the issue is on the substraction part, this is the error I'm getting

ERROR: You cannot reopen WORK.NPT.DATA for update access with member-level control because
WORK.NPT.DATA is in use by you in resource environment SQL (2).
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.

I'm working on EG version 7.12

[ballardw]

---

@Cristiansg wrote:

Hi all,

I'm trying to update a column from table a with the result of the substraction of another colum from table a minus a column from table b, under some conditions.

This is similar to what I have:

proc sql;
Update OPS

set
LEG_2 = ((select sumofdata from OPS) - (select legshares from NPT )),
LEG_1_MINUS_LEG_2_SHARES=0
where exists
(select * from OPS where
NPT .AliasissueID = OPS.aliasissueid and
NPT .ACCOUNTID = OPS.ACCOUNTID and
NPT .legshares > 0 and
OPS.ASSETMINORCLSSDESC = 'CLASS_I' and
NPT .Spread= 'Yes');
quit;

sumofdata, legshares  and LEG_2  are numeric.

The first two where conditions are just to do the join, and the others 3 are related to the process. I think the issue is on the substraction part, this is the error I'm getting

ERROR: You cannot reopen WORK.NPT.DATA for update access with member-level control because
WORK.NPT.DATA is in use by you in resource environment SQL (2).
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.

I'm working on EG version 7.12

---

You have the table open. Close it before running the code.

Also you will need to address the undo policy.

[Cristiansg]

Hi ballardw, thanks for your quick reply, but I forgot to mention that. I already reestarted SAS and the computer, but the error is still there. My theory (not sure at all) is that the table is being used to do the substraction at the same time that is being updated, so it crashes. I was trying to move the data to a third table, but it didn't work

[ballardw]

NPT .AliasissueID = OPS.aliasissueid and
NPT .ACCOUNTID = OPS.ACCOUNTID and
NPT .legshares > 0 and
OPS.ASSETMINORCLSSDESC = 'CLASS_I' and

Since you have 3 instances with a space between NPT and a variable name in the code above you are confusing the compiler as to intent. Start by getting the basic syntax correct.

If I recall correctly, SAS Proc SQL will currently only allow updating a single variable as well. So you may need to drop the update for

LEG_1_MINUS_LEG_2_SHARES=0

[Cristiansg]

Yeah, the space was a typo when I copied it to this forum, but I checked in sas and it's correct (without the spaces). I removed the update for 

LEG_1_MINUS_LEG_2_SHARES=0

but the error persists.

Thanks again