topic RE: Cumulative sum with multiple criteria in SAS Programming

RE: Cumulative sum with multiple criteria

gsisme — Tue, 21 Jul 2020 12:55:31 GMT

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

RE: Cumulative sum with multiple criteria

PaigeMiller — Tue, 21 Jul 2020 13:11:53 GMT

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;

RE: Cumulative sum with multiple criteria

gsisme — Tue, 21 Jul 2020 13:23:04 GMT

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.