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.
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 -
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.
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.
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;
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 -
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?
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.