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?
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.