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
- /
- Cumulative sum by three groups

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

07-07-2017 05:48 AM

Hello,

I want to calculate cumulative sum by three variables. The following data give some ideas about what I want:

ISIN | Fyear | Group | Values | Want |

AAA | 2010 | 1 | 10 | 10 |

AAA | 2010 | 2 | 11 | 21 |

AAA | 2010 | 3 | 12 | 33 |

AAA | 2011 | 1 | 15 | 15 |

AAA | 2011 | 2 | 18 | 33 |

AAA | 2011 | 3 | 19 | 52 |

BBB | 2010 | 1 | 20 | 20 |

BBB | 2010 | 2 | 30 | 50 |

BBB | 2010 | 3 | 40 | 90 |

BBB | 2011 | 1 | 10 | 10 |

BBB | 2011 | 2 | 15 | 25 |

BBB | 2011 | 3 | 20 | 45 |

Basically, I want a variable "want" which is the cumulative sum of "values" by ISIN, Fyear and group. I found some SAS codes from SAS community, but those codes are used to calculate the cumulative sum by one or two variables. I tried to modify and test those codes, but they did not work correctly. I think I need a new code. I wonder if anyone can help me a little bit. Thank you very much for your help!

Cheers, Thierry.

Accepted Solutions

Solution

07-07-2017
06:43 AM

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

07-07-2017 06:22 AM - edited 07-07-2017 06:23 AM

If you examine the results you are seeking, you are actually getting a cumulative sum by 2 variables, not 3. GROUP has nothing to do with the logic. So assuming your data set is already sorted (making the BY statement legal):

data want;

set have;

by isin fyear;

if first.fyear then want=values;

else want + values;

run;

All Replies

Solution

07-07-2017
06:43 AM

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

07-07-2017 06:22 AM - edited 07-07-2017 06:23 AM

If you examine the results you are seeking, you are actually getting a cumulative sum by 2 variables, not 3. GROUP has nothing to do with the logic. So assuming your data set is already sorted (making the BY statement legal):

data want;

set have;

by isin fyear;

if first.fyear then want=values;

else want + values;

run;

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

07-07-2017 06:43 AM

Thank you very much! It is very useful!