topic Re: Calculating CUM SUM for multiple groups in SAS Programming

Calculating CUM SUM for multiple groups

crawfe — Tue, 15 Oct 2019 18:12:24 GMT

Hello. This is a segment of a table with Name, month/years, value.

What is a straightforward way to get the cum sum to reset at the next Name?

The MONTHYEAR is sorted sequentially. Thanks!

HAVE: WANTED CUMSUM COLUMN:

NAME    MONTHYEAR    VALUE      CUMSUM
Name1   06/2019                   2               2
Name1 07/2019 0               2
Name1   08/2019                   5             7
Name1 09/2019 6             13
Name1   10/2019    1             14
Name2   10/2017    1              1
Name2   11/2017                  0              1
Name2   12/2017                  2              4
Name2 01/2018                  4           8
Name2   02/2018                  1       9
Name3....etc.

Re: Calculating CUM SUM for multiple groups

novinosrin — Tue, 15 Oct 2019 19:03:54 GMT


data have;
input NAME $    MONTHYEAR  $  VALUE;*      CUMSUM;
cards;
Name1   06/2019                   2               2
Name1   07/2019                   0               2
Name1   08/2019                   5               7
Name1   09/2019                   6             13
Name1   10/2019                  1             14
Name2   10/2017                  1              1   
Name2   11/2017                  0              1   
Name2   12/2017                  2               4           
Name2   01/2018                  4               8      
Name2   02/2018                  1               9
;

data want;
set have;
by name;
if first.name then CUMSUM=value;
else CUMSUM+value;
run;

Re: Calculating CUM SUM for multiple groups

Reeza — Tue, 15 Oct 2019 19:14:10 GMT

BY group processing does this in SAS.
See this page for examples and how it works. Novinosrin's code is likely what you need to answer the question. This explains how it works.
https://documentation.sas.com/?docsetId=lrcon&docsetTarget=p0xu93fy5eemkyn1p6mj5elses7j.htm&docsetVersion=9.4&locale=en

Re: Calculating CUM SUM for multiple groups

crawfe — Wed, 16 Oct 2019 00:06:14 GMT

I still get stuck trying to make things more complicated than they need to be 😞

Thanks! That should work fine.

Re: Calculating CUM SUM for multiple groups

crawfe — Wed, 16 Oct 2019 00:06:46 GMT

Good reference. Thanks!