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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.