BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ger15xxhcker
Quartz | Level 8

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;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Kurt_Bremser
Super User

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

View solution in original post

3 REPLIES 3
Kurt_Bremser
Super User

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
Quartz | Level 8
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
Super User

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1611 views
  • 0 likes
  • 2 in conversation