Sum by Group data step

[sklal]

Hi all,

I have a data in the form of the following:

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

 

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.

[RW9]

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.

[sklal]

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.

[RW9]

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;

[Oligolas · Accepted solution]

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;

[sklal]

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?