topic Re: Update a variable using a substraction of two different datasets in SAS Programming

Update a variable using a substraction of two different datasets

Cristiansg — Thu, 11 Apr 2019 21:16:53 GMT

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

Re: Update a variable using a substraction of two different datasets

ballardw — Thu, 11 Apr 2019 21:23:44 GMT

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

Re: Update a variable using a substraction of two different datasets

Cristiansg — Thu, 11 Apr 2019 21:54:44 GMT

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

Re: Update a variable using a substraction of two different datasets

ballardw — Thu, 11 Apr 2019 23:43:13 GMT

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

Re: Update a variable using a substraction of two different datasets

Cristiansg — Fri, 12 Apr 2019 13:29:42 GMT

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