Cumilative sum and group by

[David_Billa]

I have a data as follows and I'm trying to find the cumilative sum of the variable 'VALUE'  with Group by of the variable 'TYPE' '. I tried with sum(value) and group by the variable 'TYPE' but it did not produce the desired results. When I say cumulative sum, means I want to sum the values of the current rows and the subsequent rows and  not the value from previous rows.

Looking for some insights to get it done. Hope someone helps

Source data:

UNIT	CAL_MONTH	TYPE	VALUE
11223	30.06.2019	LR	20
11223	30.09.2019	LR	20
11223	31.12.2019	LR	20
11223	31.03.2020	LR	20
11223	30.06.2020	LR	46
11223	30.09.2020	LR	46
11223	31.12.2020	LI	46
11223	31.03.2021	LI	26
11223	30.06.2021	LI	40
11223	30.09.2021	LI	40
11223	31.12.2021	LI	40
11223	31.03.2022	LI	14

Desired result is,

UNIT	CAL_MONTH	TYPE	VALUE
11223	30.06.2019	LR	172
11223	30.09.2019	LR	152
11223	31.12.2019	LR	132
11223	31.03.2020	LR	112
11223	30.06.2020	LR	66
11223	30.09.2020	LR	20
11223	31.12.2020	LI	206
11223	31.03.2021	LI	160
11223	30.06.2021	LI	134
11223	30.09.2021	LI	94
11223	31.12.2021	LI	54
11223	31.03.2022	LI	40

[PaigeMiller]

Please provide the data in a useable form ... essentially we need working SAS data step code. Thanks!

[David_Billa]

I'm looking for logic so that I can manage myself to implement it.

[PaigeMiller]

---

@David_Billa wrote:
I'm looking for logic so that I can manage myself to implement it.

---

This doesn't really address my issue, nor does it seem to address @ballardw's issue. 

[ballardw]

Please describe in excruciating detail how the want value of

11223	30.09.2020	LR	20 

 is arrived at.

Since there is an existing value of

11223	30.09.2020	LR	46

That 20 is not in any way a "sum". So I am not surprised that a by group approach did not yield what you want since your not actually summing things in the normal meaning of "sum".

[Reeza · Accepted solution]

proc sql;
create table _temp as 
select *, sum(value) as total_value
from have
group by unit, type;
quit;

data want;
set have;
by unit type;
retain remaining;

if first.type then remaining = total_value;
else remaining = total_value - value;

run;

---

@David_Billa wrote:

I have a data as follows and I'm trying to find the cumilative sum of the variable 'VALUE'  with Group by of the variable 'TYPE' '. I tried with sum(value) and group by the variable 'TYPE' but it did not produce the desired results.

Looking for some insights to get it done. Hope someone helps

Source data:

UNIT	CAL_MONTH	TYPE	VALUE
11223	30.06.2019	LR	20
11223	30.09.2019	LR	20
11223	31.12.2019	LR	20
11223	31.03.2020	LR	20
11223	30.06.2020	LR	46
11223	30.09.2020	LR	46
11223	31.12.2020	LI	46
11223	31.03.2021	LI	26
11223	30.06.2021	LI	40
11223	30.09.2021	LI	40
11223	31.12.2021	LI	40
11223	31.03.2022	LI	14

Desired result is,

UNIT	CAL_MONTH	TYPE	VALUE
11223	30.06.2019	LR	172
11223	30.09.2019	LR	152
11223	31.12.2019	LR	132
11223	31.03.2020	LR	112
11223	30.06.2020	LR	66
11223	30.09.2020	LR	20
11223	31.12.2020	LI	206
11223	31.03.2021	LI	160
11223	30.06.2021	LI	134
11223	30.09.2021	LI	94
11223	31.12.2021	LI	54
11223	31.03.2022	LI	40

---

[David_Billa]

May I know why you have used proc sql step to create _temp table?

[Reeza]

To add the total in to the table to use to decrement your count.

[David_Billa]

Bit confused. You have not used _temp table in second data step.

Where you related _temp in the second data step?

[Reeza]

My bad, second data set should be using _temp not have.

[David_Billa]

Thanks. It is helpful.

Is there a way that we can do it in one step or alternative ways to
accomplish it?

[Reeza]

Yes, there are many other ways, but IMO this is likely the simplest one for someone to understand and be able to modify and extend themselves.

[s_lassen]

You can accomplish your goal like this:

data have;
informat Cal_Month ddmmyy10.;
format Cal_Month ddmmyy10.;
input UNIT	CAL_MONTH TYPE $	VALUE;
cards;
11223 30.06.2019 LR 20
11223 30.09.2019 LR 20
11223 31.12.2019 LR 20
11223 31.03.2020 LR 20
11223 30.06.2020 LR 46
11223 30.09.2020 LR 46
11223 31.12.2020 LI 46
11223 31.03.2021 LI 26
11223 30.06.2021 LI 40
11223 30.09.2021 LI 40
11223 31.12.2021 LI 40
11223 31.03.2022 LI 14
;run;

data want;
  set have;
  by unit type notsorted;
  if first.type then do;
    sum=0;
    do until(last.type2);
      set have(rename=(type=type2) keep=unit type value);
      by unit type2 notsorted;
      sum+value;
      end;
    end;
  output;
  sum=sum-value;
  drop value type2;
  rename sum=value;
run;

The NOTSORTED option on the BY statements is because the data is not sorted by TYPE - I assume that you want to keep the date order. SUM is automatically retained because of the SUM statement ("sum+value"). I renamed TYPE to TYPE2 on the second SET statement, so that there will be no confusion between the two SET statements' FIRST. condition.