EMB2 is a table of accounts and repeated by as_of_dt.
Account_Num | As_of_Dt | Account_Status | New_Downgrade_Flag |
---|---|---|---|
S77856455E | 31JAN2011 | Passed | . |
TX2342331F | 31JAN2011 | Passed | . |
S77856455E | 28FEB2011 | Passed | . |
TX2342331F | 28FEB2011 | Loss | 1 |
If an account is newly classified as Loss in the current month but was passed in previous month, then it is a new downgrade.
PROC SQL;
UPDATE EMB2 AS U
SET NEW_DOWNGRADE_FLAG = '1'
WHERE U.ACCOUNT_NUM IN (SELECT B.ACCOUNT_NUM FROM EMB2 AS B
WHERE B.ACCOUNT_STATUS IN ('Passed', 'Special Mention (Tech)', 'Special Mention')
AND B.AS_OF_DT = '31JAN2011'D)
AND U.ACCOUNT_STATUS IN ('Doubtful', 'Sub-Standard', 'Loss')
AND U.AS_OF_DT = '28FEB2011'D
;
RUN;
ERROR: You cannot reopen EMB2.DATA for update access with member-level control because EMB2.DATA is in use by you in
resource environment SQL (2).
I know how it can be done in DATA STEP: sorting by Account_Num and As-Of_Dt. And using 'IF First.Account_Status.
But I prefer to use PROC SQL. I feel PROC SQL is more robust.
From your LOG, It is to say that the table is using now, you can't open or update it because it is locked.
Could you close all of your EG (or sas session) and start a new EG?
If the error still appeared , then someone else is still using it.
Good Luck.
Ksharp
No, I think the problem is that you can't update a table with data from the same query.
But do you really need a subquery?
I think if you just move the subquery where clause to the outer query, you will accomplish the same thing...
How would the code look like to 'move the subquery where clause to the outer query'?
By the way, the As_Of_Dt is many end of months (EOM). But in my example I only showed 2 EOM.
PROC SQL;
SELECT "'" || strip(B.ACCOUNT_NUM) || "'"
into :ACCOUNT_NUM separated by ','
FROM EMB2 AS B
WHERE B.ACCOUNT_STATUS IN ('Passed', 'Special Mention (Tech)', 'Special Mention')
AND B.AS_OF_DT = '31JAN2011'D;
UPDATE EMB2 AS U
SET NEW_DOWNGRADE_FLAG = '1'
WHERE U.ACCOUNT_NUM IN (&ACCOUNT_NUM )
AND U.ACCOUNT_STATUS IN ('Doubtful', 'Sub-Standard', 'Loss')
AND U.AS_OF_DT = '28FEB2011'D;
QUIT;
My sugestion
In my opinion, it will never update any row unless the account statuses and "as of" dates have been updated between the moment of your selection and the moment you execute the update statement. This because 'ACCOUNT_STATUS' and 'AS_OF_DT' are used in the where clauses of both the inner select and the update statement.
You can't update a table with itself. You need to create a copy of the table as done in below code.
The technique Geraldo proposes would also work as long as the string of account numbers doesn't exceed 32K (the max. storage capacity of a SAS macro variable).
In your case with Account_Num of length 10 plus 2 quotes and a comma per account number you could maximally store 2520 distinct values.
data EMB2;
infile datalines dsd dlm=',';
input Account_Num:$10. As_of_Dt:date9. Account_Status $;
format As_of_Dt date9.;
retain New_Downgrade_Flag ' ';
datalines;
S77856455E,31Jan2011,Passed
TX2342331F,31Jan2011,Passed
S77856455E,28Feb2011,Passed
TX2342331F,28Feb2011,Loss
;
run;
PROC SQL;
create table EMB2_int as
SELECT ACCOUNT_NUM FROM EMB2
WHERE ACCOUNT_STATUS IN ('Passed', 'Special Mention (Tech)', 'Special Mention')
AND AS_OF_DT = '31JAN2011'D
;
UPDATE EMB2 AS U
SET NEW_DOWNGRADE_FLAG = '1'
where
U.ACCOUNT_STATUS IN ('Doubtful', 'Sub-Standard', 'Loss')
AND U.AS_OF_DT = '28FEB2011'D
AND U.ACCOUNT_NUM in (select I.ACCOUNT_NUM from EMB2_int I)
;
drop table EMB2_int
;
quit;
proc print data=EMB2;
run;
Patrick,
Ok, very good.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: