Why it doesnt work?

mender97 — Fri, 21 Jun 2019 11:42:17 GMT

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:

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:

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

Can you help, please?

Re: Why it doesnt work?

VDD — Sat, 22 Jun 2019 16:49:05 GMT

@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.

Re: Why it doesnt work?

tsap — Tue, 25 Jun 2019 13:10:30 GMT

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.

topic Why it doesnt work? in SAS Programming

Why it doesnt work?

Re: Why it doesnt work?

Re: Why it doesnt work?