reverse retaining with example

[ger15xxhcker]

Hi!

Can you help me this group retaining? How can i solve it? I hope its a good exemple.

Would really appreciate any suggestions. Thank you.

data start_dataset;

input account date amt sum;

datalines;

510046500   20190416    43728.16    6262.12

510046500   20190516    43771.27    37577.49

510046500   20190614    43771.27    43771.27

510046500   20190716    43771.27    43771.27

510046500   20190816    43810.92    43810.92

510046500   20190916    43810.92    43810.92

510046500   20191016    43810.92    43810.92

;

run;



/*

If the difference in sum field <0 then stop end drop this record. Sum can not be negative.

sum(all same acocunt amt) - previus date amt

*/



data final;

input account date amt sum;

datalines;

510046500   20190416    43728.16    68.34

510046500   20190516    43771.27    43839.61

510046500   20190614    43771.27    87610.88

510046500   20190716    43771.27    131382.15

510046500   20190816    43810.92    175193.07

510046500   20190916    43810.92    219003.99

510046500   20191016    43810.92    262814.91

;

run;

[Kurt_Bremser]

How do you calculate sum in the first observation of a group, and how do you calculate the next ones?

Right now I can't make sense of your description.

[ger15xxhcker]

262814.91-43810.92-43810.92 ...etc. 262814.91 Number came from the same Account numbers cummulated Amt fields. Thanks for your help

[Kurt_Bremser · Accepted solution]

See this:

data have;
input account :$9. date yymmdd10. amt sum;
format date yymmddd10.;
datalines;
510046500   20190416    43728.16    6262.12
510046500   20190516    43771.27    37577.49
510046500   20190614    43771.27    43771.27
510046500   20190716    43771.27    43771.27
510046500   20190816    43810.92    43810.92
510046500   20190916    43810.92    43810.92
510046500   20191016    43810.92    43810.92
;

proc sort data=have;
by account descending date;
run;

data want;
if 0 then set have;
total_sum = 0;
do until (last.account);
  set have;
  by account;
  total_sum + sum;
end;
do until (last.account);
  set have;
  by account;
  sum = total_sum;
  output;
  total_sum + (-amt);
end;
drop total_sum;
run;

proc sort data=want;
by account date;
run;

proc print data=want noobs;
run;

Result:

account  	date         	amt          	sum
510046500	2019-04-16	43728.16	68.34
510046500	2019-05-16	43771.27	43839.61
510046500	2019-06-14	43771.27	87610.88
510046500	2019-07-16	43771.27	131382.15
510046500	2019-08-16	43810.92	175193.07
510046500	2019-09-16	43810.92	219003.99
510046500	2019-10-16	43810.92	262814.91