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?
... View more
