turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- Base SAS Programming
- /
- Define Close Date for Each Account

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-31-2016 11:07 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-01-2016 12:10 AM

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

10-31-2016 11:22 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-01-2016 12:10 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-01-2016 10:25 AM

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

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-01-2016 03:43 AM

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?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-01-2016 10:23 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

11-01-2016 10:27 AM

Ah ok makes sense