PROC SQL: Update table from same table

[hellind]

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.

[Ksharp]

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]

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

[hellind]

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]

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]

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]

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]

Patrick,

Ok, very good.