SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Calculating cumulative sum

Accepted Solution Solved
Reply
Super Contributor
Posts: 413
Accepted Solution

Calculating cumulative sum

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


Accepted Solutions
Solution
‎12-11-2014 12:51 AM
Trusted Advisor
Posts: 1,204

Re: Calculating cumulative sum

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;

View solution in original post


All Replies
Super User
Posts: 17,837

Re: Calculating cumulative sum

data want;

set have;

retain cum_sum;

cum_sum+value;

run;

Super Contributor
Posts: 413

Re: Calculating cumulative sum

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

Super User
Posts: 17,837

Re: Calculating cumulative sum

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;

Super Contributor
Posts: 413

Re: Calculating cumulative sum

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

Solution
‎12-11-2014 12:51 AM
Trusted Advisor
Posts: 1,204

Re: Calculating cumulative sum

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;

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 11509 views
  • 6 likes
  • 3 in conversation