Hi, morning.
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;
Check these links : PROC SQL - UPDATE
PROC SQL UPDATE PROBLEM
This is another question about the same metter.
Hope this might help you Mate