topic Re: Sum by Group data step in SAS Programming

Sum by Group data step

sklal — Fri, 27 Apr 2018 09:13:15 GMT

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.

Re: Sum by Group data step

RW9 — Fri, 27 Apr 2018 09:27:18 GMT

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.

Re: Sum by Group data step

Oligolas — Fri, 27 Apr 2018 09:37:22 GMT

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;

Re: Sum by Group data step

sklal — Fri, 27 Apr 2018 09:51:00 GMT

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.

Re: Sum by Group data step

RW9 — Fri, 27 Apr 2018 10:26:57 GMT

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;

Re: Sum by Group data step

sklal — Sat, 28 Apr 2018 04:35:50 GMT

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?