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;
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
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.
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
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.