Code 36 is always going to appear first, but it may also appear multiple times for this same customer on different dates.
It may go 36, 36, 36 (3 different dates) then move into, 36, 18, 36, 36, 15. 36 Basically means they were charged a late fee, after said late fee they will either Pay, Waive or it will remain outstanding (as they will get another 36 or late fee).
Basically, I am trying to calculate the late fees (amount is almost always $50) and determine what our balances are outstanding, paid and waived. In the example above, the first 3 late fees (-$150), then have a 4th late fee (-$200), Paid (We'll just say $50) so we are now at (-$150), 2 more late fees (-$250) a Waive (which will zero out the LAST late fee) so (-$200).
So our outstanding balance in this example is (-$200), with $50 paid and $50 waived.
I have written some of this in SQL but my big roadblock is I can't do a cumulative sum(?) if that's the right terminology to get the Paid amounts to calculate. I THINK I could likely break it down and start doing LEAD() functions pull the next number and SUM those then delete that row and rinse and repeat but was hopeful there might be a more simple approach with SAS and I could learn something with SAS as well.
... View more
