BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Grasshopper
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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
Super User
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
Calcite | Level 5

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

 

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

PeterClemmensen
Tourmaline | Level 20

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
Calcite | Level 5

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

PeterClemmensen
Tourmaline | Level 20

Ah ok makes sense 🙂

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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