turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- Data Management
- /
- Forum
- /
- Calculating cumulative sum

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

12-10-2014 11:12 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

12-11-2014 12:51 AM

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;

All Replies

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

12-10-2014 11:19 PM

data want;

set have;

retain cum_sum;

cum_sum+value;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

12-10-2014 11:52 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

12-11-2014 12:05 AM

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;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Reeza

12-11-2014 12:06 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

Posted in reply to ilikesas

12-11-2014 12:51 AM

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;