DATA Step, Macro, Functions and more

Define Close Date for Each Account

Accepted Solution Solved
Reply
New Contributor
Posts: 3
Accepted Solution

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 MonthBalance
Acct11/1/20011
Acct12/1/20012
Acct13/1/20013
Acct14/1/20010
Acct15/1/20010
Acct16/1/20010
Acct17/1/20017
Acct18/1/20016
Acct19/1/20015
Acct110/1/20010
Acct111/1/20010
Acct112/1/20010
Acct11/1/20123
Acct12/1/20122
Acct 21/1/20012
Acct22/1/20014

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

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;


View solution in original post


All Replies
Super User
Super User
Posts: 7,401

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: 9,681

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 repliesSmiley Happy You guys really helped me!

PROC Star
Posts: 551

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/2001Smiley Happy

PROC Star
Posts: 551

Re: Define Close Date for Each Account

Ah ok makes sense Smiley Happy

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 276 views
  • 0 likes
  • 4 in conversation