topic Re: PROC SQL: Update table from same table in SAS Procedures

PROC SQL: Update table from same table

hellind — Fri, 09 Mar 2012 04:46:09 GMT

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.

PROC SQL: Update table from same table

Ksharp — Fri, 09 Mar 2012 07:26:59 GMT

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

PROC SQL: Update table from same table

LinusH — Fri, 09 Mar 2012 07:58:15 GMT

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

PROC SQL: Update table from same table

Geraldo — Fri, 09 Mar 2012 14:21:45 GMT

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

PROC SQL: Update table from same table

Florent — Fri, 09 Mar 2012 16:31:00 GMT

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.

Re: PROC SQL: Update table from same table

Patrick — Sat, 10 Mar 2012 12:58:24 GMT

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;

Re: PROC SQL: Update table from same table

Geraldo — Sat, 10 Mar 2012 14:35:00 GMT

Patrick,

Ok, very good.

Re: PROC SQL: Update table from same table

hellind — Sun, 11 Mar 2012 06:23:56 GMT

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.