Why it doesnt work?

[mender97]

Hi. 
I have the table 

id_client    |  id_payment | max_sum | from_dt        | to_dt           | Payment_sum

37212568 | 5061016939 | 60000       | 2019-06-14 | 2019-06-17 | 59079.99
37212568 | 5059309186 | 60000       | 2019-06-14 | 2019-06-17 | 7535
37212568 | 5096581965 | 80000       | 2019-06-18 | 2019-12-01 | 16773.95
37212568 | 5095504587 | 80000       | 2019-06-18 | 2019-12-01 | 20000
37212568 | 5094637028 | 80000       | 2019-06-18 | 2019-12-01 | 3523
37212568 | 5087834012 | 80000       | 2019-06-18 | 2019-12-01 | 13111.16
37212568 | 5096774461 | 80000       | 2019-06-18 | 2019-12-01 | 2168
37212568 | 5087460886 | 80000       | 2019-06-18 | 2019-12-01 | 61960
37212568 | 5088215409 | 80000       | 2019-06-18 | 2019-12-01 | 21077
37212568 | 5094892350 | 80000       | 2019-06-18 | 2019-12-01 | 299.84
37212568 | 5089225101 | 80000       | 2019-06-18 | 2019-12-01 | 1005.8

And i have the follow code:

proc sort data=table_1; by account_rk from_dt; run;
data table_2;
set table_1;
retain acct 0  payed 0 to_pay 0;
if client_id= acct and payed+payment_sum>= max_sum then payed = max_sum; 
else if client_id= acct and payed+payment_sum< max_sum then payed = payed + to_pay; 
else payed = 0;
acct = client_id;
if max_sum > (payment_sum+ payed) then to_pay = payment_sum;
if max_sum <= (payment_sum+ payed) and max_sum - payed >= 0 then to_pay = max_sum - payed;
if max_sum <= (payment_sum+ payed) and max_sum - payed < 0 then to_pay = 0;
run;

How it works:

id_client    |  id_payment | max_sum | from_dt        | to_dt     | Payment_sum | acct           |     payed        | to_pay                 

37212568 | 5061016939 | 60000 | 2019-06-14 | 2019-06-17 | 59079.99         | 37212568 | 0                    | 59079.99
37212568 | 5059309186 | 60000 | 2019-06-14 | 2019-06-17 | 7535                | 37212568 | 59079.99      | 920.01
37212568 | 5096581965 | 80000 | 2019-06-18 | 2019-12-01 | 16773.95         | 37212568 | 60000           | 16773.95
37212568 | 5095504587 | 80000 | 2019-06-18 | 2019-12-01 | 20000              | 37212568 | 80000           | 0
37212568 | 5094637028 | 80000 | 2019-06-18 | 2019-12-01 | 3523                | 37212568 | 80000           | 0
37212568 | 5087834012 | 80000 | 2019-06-18 | 2019-12-01 | 13111.16          | 37212568 | 80000           | 0
37212568 | 5096774461 | 80000 | 2019-06-18 | 2019-12-01 | 2168                | 37212568 | 80000           | 0
37212568 | 5087460886 | 80000 | 2019-06-18 | 2019-12-01 | 61960              | 37212568 | 80000           | 0
37212568 | 5088215409 | 80000 | 2019-06-18 | 2019-12-01 | 21077              | 37212568 | 80000            | 0
37212568 | 5094892350 | 80000 | 2019-06-18 | 2019-12-01 | 299.84             | 37212568 | 80000            | 0
37212568 | 5089225101 | 80000 | 2019-06-18 | 2019-12-01 | 1005.8             | 37212568  | 80000            | 0

In highlighted row it works incorrecty.

becacuse it should be:

id_client    |  id_payment | max_sum | from_dt  | to_dt      | Payment_sum | acct           |     payed        | to_pay                 

37212568 | 5061016939 | 60000 | 2019-06-14 | 2019-06-17 | 59079.99    | 37212568 | 0                    | 59079.99
37212568 | 5059309186 | 60000 | 2019-06-14 | 2019-06-17 | 7535           | 37212568 | 59079.99      | 920.01
37212568 | 5096581965 | 80000 | 2019-06-18 | 2019-12-01 | 16773.95    | 37212568 | 60000           | 16773.95
37212568 | 5095504587 | 80000 | 2019-06-18 | 2019-12-01 | 20000         | 37212568 | 76773.95     | 3226.35

37212568 | 5094637028 | 80000 | 2019-06-18 | 2019-12-01 | 3523            | 37212568 | 80000           | 0

.

.

.

Can you help, please?

[VDD]

@mender97 when you do 

proc sort data=table_1; by account_rk from_dt; run;

where is the account_rk on the datasets? This may be your issue.

the record you have highlighted reflects the client is due a refund because they have over payed.

[tsap]

There are a couple of things that confuse me in your post:

1. Does the ID_Payment not play as a factor in the sorting order?
          - There are two payments between '14JUN2019' and '17JUN2019', then nine payments between '18JUN2019' and  

            '01DEC2019'.
          - Since we are calculating a running calculation for how much debt was payed off at what point and what debt was remaining,
            wouldn't we need to sort across ID_Payment? (assuming that wasn't just a random number, which by looking at it, does not  

            appear to be random).

2. If I am reading this correctly it appears as though the field, 'Payment_Sum' is the amount paid in that specific transaction.
          - If that is the purpose of that field, why would we want the first observation in your results to show 'payed' as 0 if the

            'payment_sum' was 59,079.99?

          - I would think that the first observation would populated 'payed' with 59,079.99 and then the following 'to_pay'(remaining debt)   

            would be 920.01

Understanding these items a little better might help me understand what needs to occur to accomplish the results you need.