Hi,
my question might be too simple but I actually have some trouble figuring it out...
what I want to do is to calculate the cumulative sum of a column, so if I have the following table:
value |
---|
10 |
20 |
30 |
40 |
I would like to add to it the following column:
cummulative sum |
---|
10 |
30 |
60 |
100 |
Thank you
Hi,
For many companies you need by processing. Just make sure data is sorted by company variable.
data want;
set have;
by company;
if first.company then cum_sum=0;
cum_sum+value;
run;
data want;
set have;
retain cum_sum;
cum_sum+value;
run;
Hi Reeza,
thanks for the answer!
It is really short but I couldn't figure it out because its not so intuitive (at least to me) because I was trying to do something like setting the first cumsum to the value itself and for the rest of values to make cumsum by adding the value to the sum( ) of the previous values and I was getting a mess...
Technically you don't even need the retain statement, the + implies the retain so the following works as well:
data want;
set have;
cum_sum+value;
run;
Hi Reeza its me again,
now I would like to add another level of complexity, if I may:
assume that in the previous case I had to calculate the cumulative sum for one company, but now I need to do the same thing but for many companies, so each time I get to a new company the cumulative sum restarts calculating.
Thank you
Hi,
For many companies you need by processing. Just make sure data is sorted by company variable.
data want;
set have;
by company;
if first.company then cum_sum=0;
cum_sum+value;
run;
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.
Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.
Find more tutorials on the SAS Users YouTube channel.