DATA Step, Macro, Functions and more

Sum by Group data step

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 18
Accepted Solution

Sum by Group data step

[ Edited ]

Hi all,

 

I have a data in the form of the following:

HAVE.jpg

I need to calculate the cumulative sum per Centre value like the following

HAVE1.jpg 

I tried writing a code for the same but it is taking the cumulative sum of all the rows without considering the Centre Value.

Please suggest a solution.


Accepted Solutions
Solution
‎04-27-2018 08:07 AM
Regular Contributor
Posts: 164

Re: Sum by Group data step

Hi,

data have;
center='A';lvalue=100;output;
center='A';lvalue=200;output;
center='A';lvalue=300;output;
center='A';lvalue=450;output;
center='B';lvalue=200;output;
center='B';lvalue=250;output;
center='B';lvalue=350;output;
center='C';lvalue=50;output;
center='C';lvalue=150;output;
center='C';lvalue=40;output;
center='C';lvalue=65;output;
center='C';lvalue=70;output;
run;
proc sort data=have;by center;run;
data want;
   set have;
   by center;
   if first.center then cumulative_sum=0;
   retain cumulative_sum 0;
   cumulative_sum=cumulative_sum+lvalue;
   
run;
________________________

- Cheers -

View solution in original post


All Replies
Super User
Super User
Posts: 9,829

Re: Sum by Group data step

You can use the Search bar, there are loads of examples.  For instance:

data want;
  set have;
  retain cum_sum;
  by center;
  cum_sum=ifn(_n_=1,lvalue,sum(cum_sum,lvalue));
run;

Also, post test data in the form of a datastep, not here to type that out just to test it.

Occasional Contributor
Posts: 18

Re: Sum by Group data step

Thanks for the suggestion, I'll keep that in mind. I tried the code that you have written but it is summing up all the rows without considering the Centre value.

Super User
Super User
Posts: 9,829

Re: Sum by Group data step

Yep forgot to reset the counter:

data want;
  set have;
  retain cum_sum;
  by center;
if first.center then cum_sum=0; cum_sum=ifn(_n_=1,lvalue,sum(cum_sum,lvalue)); run;
Solution
‎04-27-2018 08:07 AM
Regular Contributor
Posts: 164

Re: Sum by Group data step

Hi,

data have;
center='A';lvalue=100;output;
center='A';lvalue=200;output;
center='A';lvalue=300;output;
center='A';lvalue=450;output;
center='B';lvalue=200;output;
center='B';lvalue=250;output;
center='B';lvalue=350;output;
center='C';lvalue=50;output;
center='C';lvalue=150;output;
center='C';lvalue=40;output;
center='C';lvalue=65;output;
center='C';lvalue=70;output;
run;
proc sort data=have;by center;run;
data want;
   set have;
   by center;
   if first.center then cumulative_sum=0;
   retain cumulative_sum 0;
   cumulative_sum=cumulative_sum+lvalue;
   
run;
________________________

- Cheers -

Occasional Contributor
Posts: 18

Re: Sum by Group data step

I applied the code to a larger data set with the relevant variables but now I'm getting the cumulative sum for all the rows irrespective of the category variable Centre. What could be the possible reason for this?

 

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 167 views
  • 0 likes
  • 3 in conversation