Solved
New Contributor
Posts: 3

Define Close Date for Each Account

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

Accepted Solutions
Solution
‎11-01-2016 10:25 AM
Super User
Posts: 10,850

Re: Define Close Date for Each Account

```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;

```

All Replies
Super User
Posts: 9,862

Re: Define Close Date for Each Account

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;```
Solution
‎11-01-2016 10:25 AM
Super User
Posts: 10,850

Re: Define Close Date for Each Account

```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;

```
New Contributor
Posts: 3

Re: Define Close Date for Each Account

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

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

PROC Star
Posts: 1,405

Re: Define Close Date for Each Account

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?

New Contributor
Posts: 3

Re: Define Close Date for Each Account

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

PROC Star
Posts: 1,405