This is a piece of the dataset:
data have;
input ID Date:ddmmyy9. Positive_Points Negative_Points;
format Date ddmmyy9.;
cards;
1 23/10/2015 80.47 0
1 24/10/2015 25 0
1 01/11/2015 135.87 0
1 02/11/2015 511.53 0
1 03/12/2015 270.41 -1000
1 12/12/2015 168.75 0
1 14/12/2015 299.2 0
1 18/12/2015 323.51 0
1 28/12/2015 502.14 -800
1 07/01/2016 403.2 0
1 09/01/2016 224.22 -900
1 14/01/2016 128.72 0
1 18/01/2016 721.25 -500
1 20/01/2016 1280.89 -24
2 26/10/2015 25 0
2 26/11/2015 33.03 0
2 03/12/2015 19.98 0
2 08/12/2015 33.71 0
2 29/12/2015 8.47 -100
2 07/01/2016 211.43 0
2 22/01/2016 7.99 -200
2 23/01/2016 15 0
2 26/01/2016 17.98 0
2 30/01/2016 62.99 0
2 14/02/2016 57.97 -100
2 16/02/2016 218.64 0
2 17/02/2016 4.99 0
2 18/02/2016 74.75 -312.4
2 09/03/2016 57.97 -100
2 19/03/2016 94.87 0
2 07/04/2016 87.4 0
2 08/04/2016 94.47 0
2 09/04/2016 35.42 -100.2
2 10/04/2016 125.97 0
2 11/04/2016 0.77 -200
2 12/04/2016 10.77 0
2 21/04/2016 13.49 0
2 22/04/2016 26.98 0
2 23/04/2016 44.99 0
2 26/04/2016 16.9 -100
2 01/05/2016 196.96 -200
2 12/05/2016 10.77 0
;run;
The idea is that if some points were partially or not at all used, they report to the next period, but at the same time I need to know how many of them were used to deduct the first negative points and how many were used for the 2nd, 3rd, etc...
Also, I need to start deducting from the oldest points.
In this case, Balance for the 1st customer should look like this:
1st row Balance=80.47-1000=-919.53
2nd row Balance=25+(-919.53)
...
5th row Balance=23.28
insert new row because what's left is used for the next negative points
Positive_Points=23.28 Balance=23.28-800;
... View more
