Obsidian | Level 7

## RE: Cumulative sum with multiple criteria

Hi,

I'm working on a piece of data where I'm try to carry out a cumulative sum with multiple criteria. Using the table below as an example, I'm trying to sum the Amount based on the Date of each ID (I need the most recent months total Amount as an output). I generally use the Retain function for this (script below)  however I don't know how this works with more than 1 By variable. Does anyone have any suggestions? ()

Data have;

set want;

by ID;

Retain Amount1;

if first.ID then Amount1=Amount;

else Amount1=Amount+Amount1;

run;

 ID Date Amount 1 01/02/2018 -72545 1 01/02/2019 -29457 1 01/03/2019 -54466 1 01/03/2019 -88336 2 01/06/2019 -83525 2 01/06/2019 77979 2 01/07/2019 47607 2 01/08/2019 -48059 2 01/03/2020 38094 3 01/02/2018 56534 3 01/09/2018 20874 3 01/03/2019 -13083 3 01/03/2019 77668 3 01/04/2020 -80950 3 01/04/2020 -57656
1 ACCEPTED SOLUTION

Accepted Solutions
Diamond | Level 26

## RE: Cumulative sum with multiple criteria

I'm not sure I follow your explanation ... it sounds like you want a cumulative sum by ID and date, and when a new date is found, the cumulative sum restarts. Is that what you want? It would help if you showed us the output you want.

Nevertheless, if my guess is right, this is how to do it.

``````proc sort data=have;
by id date;
run;
data want;
set have;
by id date;
if first.date then cumulative_sum=0;
cumulative_sum+amount;
run;``````
--
Paige Miller
2 REPLIES 2
Diamond | Level 26

## RE: Cumulative sum with multiple criteria

I'm not sure I follow your explanation ... it sounds like you want a cumulative sum by ID and date, and when a new date is found, the cumulative sum restarts. Is that what you want? It would help if you showed us the output you want.

Nevertheless, if my guess is right, this is how to do it.

``````proc sort data=have;
by id date;
run;
data want;
set have;
by id date;
if first.date then cumulative_sum=0;
cumulative_sum+amount;
run;``````
--
Paige Miller
Obsidian | Level 7

## RE: Cumulative sum with multiple criteria

Hi,
Apologies if i was unclear above, however you interpreted it correctly. I've applied the solution and it's doing exactly what I need. Thank you.
Discussion stats
• 2 replies
• 713 views
• 0 likes
• 2 in conversation