topic Re: Need help with data set. in SAS Enterprise Guide

Need help with data set.

mender97 — Fri, 21 Jun 2019 11:46:35 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: Need help with data set.

Kurt_Bremser — Fri, 21 Jun 2019 12:27:01 GMT

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.

Re: Need help with data set.

mender97 — Fri, 21 Jun 2019 12:30:47 GMT

How I can to fix this?

Re: Need help with data set.

Kurt_Bremser — Fri, 21 Jun 2019 12:38:40 GMT

How would you arrive at payed = 76773.95 when the two source values are 80000 and 20000, respectively?

Re: Need help with data set.

mender97 — Fri, 21 Jun 2019 12:42:12 GMT

payed = payed(from previous row) + to_pay(from previous row)

so I need to get payed = 76773.95 = 60000 + 16773.95.

Re: Need help with data set.

Kurt_Bremser — Tue, 02 Jul 2019 08:42:41 GMT

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.