Help using Base SAS procedures

PROC SQL: Update table from same table

Reply
Frequent Contributor
Posts: 90

PROC SQL: Update table from same table

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.

Super User
Posts: 9,687

PROC SQL: Update table from same table

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

Super User
Posts: 5,260

PROC SQL: Update table from same table

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
Frequent Contributor
Posts: 90

Re: PROC SQL: Update table from same table

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.

Occasional Contributor
Posts: 11

PROC SQL: Update table from same table

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

Frequent Contributor
Posts: 127

PROC SQL: Update table from same table

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.

Respected Advisor
Posts: 3,900

Re: PROC SQL: Update table from same table

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;

Occasional Contributor
Posts: 11

Re: PROC SQL: Update table from same table

Patrick,

Ok, very good.

Ask a Question
Discussion stats
  • 7 replies
  • 1623 views
  • 0 likes
  • 6 in conversation