Define Close Date for Each Account

[Grasshopper]

Hi All, I am working on bank account data and trying to define the close date using SAS. The data looks like the table below.

The definition of closed account is that if an account has 3 months of zero balance in a row, it will be considered as closed, regardless of what would happen after that. For example, the close date for acct 1 would be 4/1/2001 although it had balance again on 7/1/2001. If account does not have a close date, it will be considered open. For closed account, I want to calculate the average balance for the last 3 month, in this case it would be the average balance from 1/1/2001 to 3/1/2001. Can anyone help me with this? Thank you!

Acct	Month	Balance
Acct1	1/1/2001	1
Acct1	2/1/2001	2
Acct1	3/1/2001	3
Acct1	4/1/2001	0
Acct1	5/1/2001	0
Acct1	6/1/2001	0
Acct1	7/1/2001	7
Acct1	8/1/2001	6
Acct1	9/1/2001	5
Acct1	10/1/2001	0
Acct1	11/1/2001	0
Acct1	12/1/2001	0
Acct1	1/1/2012	3
Acct1	2/1/2012	2
Acct 2	1/1/2001	2
Acct2	2/1/2001	4

[RW9]

HI,

Something like:

data want;
  set have;
  by acct;
  retain closed;
  if first.acct then closed=0;
  if balance=0 and lag1(balance)=0 and lag2(balance)=0 then closed=1;
run;

[Ksharp · Accepted solution]

OK. Assuming table has been sorted by acct and month.
But you didn't post the output yet.


data have;
infile cards expandtabs truncover;
input Acct $ Month : mmddyy10.	Balance;
format month mmddyy10.;
cards;
Acct1	1/1/2001	1
Acct1	2/1/2001	2
Acct1	3/1/2001	3
Acct1	4/1/2001	0
Acct1	5/1/2001	0
Acct1	6/1/2001	0
Acct1	7/1/2001	7
Acct1	8/1/2001	6
Acct1	9/1/2001	5
Acct1	10/1/2001	0
Acct1	11/1/2001	0
Acct1	12/1/2001	0
Acct1	1/1/2012	3
Acct1	2/1/2012	2
Acct 2	1/1/2001	2
Acct2	2/1/2001	4
;
run;
data temp;
do n=1 by 1 until(last.balance);
 set have;
 by acct balance notsorted;
 if first.balance then close_date=month;
end;
if n gt 2 and balance=0 then output;
format close_date mmddyy10.;
run;
data x;
 set temp;
 by acct;
 if first.acct;
 keep acct close_date;
run;
proc sql;
create table want as
 select *,(select avg(balance) 
 from have where acct=x.acct and month 
  between intnx('month',x.close_date,-3) and 
  intnx('month',x.close_date,-1)) as avg
  from x;
quit;

[Grasshopper]

Thanks Ksharp. This is exactly what I was looking for!!!

Also thanks to all the replies:) You guys really helped me!

[PeterClemmensen]

If an account has to have 3 montths of zero balance to be considered closed, wouldn't Acct1's close date be 6/1/2001?

[Grasshopper]

It is the starting month of the 3 zero balance month, in this case 4/1/2001:)

[PeterClemmensen]

Ah ok makes sense 🙂