topic Re: Define Close Date for Each Account in SAS Programming

Define Close Date for Each Account

Grasshopper — Mon, 31 Oct 2016 15:07:50 GMT

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

Re: Define Close Date for Each Account

RW9 — Mon, 31 Oct 2016 15:22:24 GMT

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;

Re: Define Close Date for Each Account

Ksharp — Tue, 01 Nov 2016 04:10:19 GMT

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;

Re: Define Close Date for Each Account

PeterClemmensen — Tue, 01 Nov 2016 07:43:48 GMT

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?

Re: Define Close Date for Each Account

Grasshopper — Tue, 01 Nov 2016 14:23:58 GMT

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

Re: Define Close Date for Each Account

Grasshopper — Tue, 01 Nov 2016 14:25:37 GMT

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

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

Re: Define Close Date for Each Account

PeterClemmensen — Tue, 01 Nov 2016 14:27:27 GMT

Ah ok makes sense 🙂