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?
This condition is satisfied:
if client_id= acct and payed+payment_sum>= max_sum then payed = max_sum;
which leads to this condition being satisfied:
if max_sum <= (payment_sum+ payed) and max_sum - payed >= 0 then to_pay = max_sum - payed;
which results in zero.
How would you arrive at payed = 76773.95 when the two source values are 80000 and 20000, respectively?
This check
if payed + payment_sum >= max_sum
was wrong, you should test for greater instead of greater or equal.
See this refined code (note the ways the original dataset is presented in a data step with datalines; please do so in the future, as it helps greatly in devising solutions):
data have;
input
id_client :$8.
id_payment :$10.
max_sum
from_dt :yymmdd10.
to_dt :yymmdd10.
Payment_sum
;
format from_dt to_dt yymmddd10.;
datalines;
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
;
data want;
set have;
by id_client;
retain payed to_pay;
if first.id_client
then do;
payed = 0;
to_pay = 0;
end;
else do;
if payed + payment_sum > max_sum
then payed = max_sum;
else payed = payed + to_pay;
end;
if max_sum > payment_sum + payed
then to_pay = payment_sum;
else do;
if max_sum - payed >= 0
then to_pay = max_sum - payed;
else to_pay = 0;
end;
run;
proc print data=want noobs;
run;
Result:
Payment_ id_client id_payment max_sum from_dt to_dt sum payed to_pay 37212568 5061016939 60000 2019-06-14 2019-06-17 59079.99 0.00 59079.99 37212568 5059309186 60000 2019-06-14 2019-06-17 7535.00 59079.99 920.01 37212568 5096581965 80000 2019-06-18 2019-12-01 16773.95 60000.00 16773.95 37212568 5095504587 80000 2019-06-18 2019-12-01 20000.00 76773.95 3226.05 37212568 5094637028 80000 2019-06-18 2019-12-01 3523.00 80000.00 0.00 37212568 5087834012 80000 2019-06-18 2019-12-01 13111.16 80000.00 0.00 37212568 5096774461 80000 2019-06-18 2019-12-01 2168.00 80000.00 0.00 37212568 5087460886 80000 2019-06-18 2019-12-01 61960.00 80000.00 0.00 37212568 5088215409 80000 2019-06-18 2019-12-01 21077.00 80000.00 0.00 37212568 5094892350 80000 2019-06-18 2019-12-01 299.84 80000.00 0.00 37212568 5089225101 80000 2019-06-18 2019-12-01 1005.80 80000.00 0.00
Note how use of the by statement and the first. variable simplifies detecting a group change. Also see how I simplified the condition tests.
Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!
Check out this tutorial series to learn how to build your own steps in SAS Studio.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.