BookmarkSubscribeRSS Feed
hellind
Quartz | Level 8

EMB2 is a table of accounts and repeated by as_of_dt.

Account_NumAs_of_DtAccount_StatusNew_Downgrade_Flag
S77856455E31JAN2011Passed.
TX2342331F31JAN2011Passed.
S77856455E28FEB2011Passed.
TX2342331F28FEB2011Loss1

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.

7 REPLIES 7
Ksharp
Super User

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

LinusH
Tourmaline | Level 20

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

Data never sleeps
hellind
Quartz | Level 8

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.

Geraldo
Fluorite | Level 6

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

Florent
Quartz | Level 8

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.

Patrick
Opal | Level 21

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;

Geraldo
Fluorite | Level 6

Patrick,

Ok, very good.

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!

What is Bayesian Analysis?

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.

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
  • 7 replies
  • 4387 views
  • 0 likes
  • 6 in conversation