Sum of current value and subsequent values and not on previous values by group

[David_Billa]

I've the data as I mentioned in the data step below. I want to calculate the field 'VALUE'  by 'TYPE' variable. Calculation of the field VALUE should be on the current value and the subsequent values by TYPE.

E.g. If you see I've 13 observations for TYPE LR. To calculate VALUE, first I have to sum of all rows (1st value to till 13th value) for the first record by TYPE then for the second record I've to sum from 2nd value to till 13th value and for 3rd record I want to sum from 3rd value to till 13th value and goes on till last record.

Client has generated the desired results in MS Excel using SUMIFS function and I find difficult to achieve it using SAS.

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 30.12.2020 LR 46
11223 31.03.2021 LR 26
11223 30.06.2021 LR 40
11223 30.09.2021 LR 40
11223 31.12.2021 LR 40
11223 31.03.2022 LR 14
11223 31.12.2020 LI 22
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;

Program which I tried is,

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;

Results which I got is,

Cal_Month	UNIT	TYPE	value
30.06.2019	11223	LR	378
30.09.2019	11223	LR	364
31.12.2019	11223	LR	344
31.03.2020	11223	LR	324
30.06.2020	11223	LR	304
30.09.2020	11223	LR	258
30.12.2020	11223	LR	212
31.03.2021	11223	LR	166
30.06.2021	11223	LR	140
30.09.2021	11223	LR	100
31.12.2021	11223	LR	60
31.03.2022	11223	LR	20
31.12.2020	11223	LI	182
31.03.2021	11223	LI	168
30.06.2021	11223	LI	142
30.09.2021	11223	LI	102
31.12.2021	11223	LI	62
31.03.2022	11223	LI	22

Results which I want is,

Cal_Month	UNIT	TYPE	value
30.06.2019	11223	LR	378
30.09.2019	11223	LR	358
31.12.2019	11223	LR	338
31.03.2020	11223	LR	318
30.06.2020	11223	LR	298
30.09.2020	11223	LR	252
30.12.2020	11223	LR	206
31.03.2021	11223	LR	160
30.06.2021	11223	LR	134
30.09.2021	11223	LR	94
31.12.2021	11223	LR	54
31.03.2022	11223	LR	14
31.12.2020	11223	LI	182
31.03.2021	11223	LI	160
30.06.2021	11223	LI	134
30.09.2021	11223	LI	94
31.12.2021	11223	LI	54
31.03.2022	11223	LI	14

I tried with retain statement and first. logic as well, but it's not working either. Only first value of each data type matches and others don't. Appericiate if someone of you help me get the desired results.

[novinosrin]

Hi @David_Billa  Please see if this helps

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 30.12.2020 LR 46
11223 31.03.2021 LR 26
11223 30.06.2021 LR 40
11223 30.09.2021 LR 40
11223 31.12.2021 LR 40
11223 31.03.2022 LR 14
11223 31.12.2020 LI 22
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;
 do _n_=1 by 1 until(last.type);
  set have;
  by unit type notsorted;
  _value=sum(_value,value);
 end;
 do _n_=1 to _n_;
  set have;
  output;
  _value=_value-value;
 end;
run;

[smantha]

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 30.12.2020 LR 46
11223 31.03.2021 LR 26
11223 30.06.2021 LR 40
11223 30.09.2021 LR 40
11223 31.12.2021 LR 40
11223 31.03.2022 LR 14
11223 31.12.2020 LI 22
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;
proc sort; by Unit Type Cal_Month;
proc means data = have  nway missing noprint;
var value;
class Unit Type;
output out=have_sum sum(value) = value_sum;
run;

data want(rename=(running_sum=value));
merge have have_sum;
by Unit Type;
retain running_sum prv_value 0;
if first.type then do;
  running_sum=value_sum;
  prv_value = Value;
end;
else do;
 running_sum=running_sum - prv_value;
 prv_value=value;
end;
keep Unit Type Cal_month running_sum;
run;

Solution you are looking for

[Jagadishkatam · Accepted solution]

alternatively try

proc sort data=have;
by UNIT TYPE CAL_MONTH;
run;

data want;
do until(last.type);
set have;
by UNIT TYPE CAL_MONTH;
if first.type then value2=.;
if value ne . then value2+value;
value3=value2;
end;
do until(last.type);
set have;
by UNIT TYPE CAL_MONTH;
output;
value3=value3-value;
drop value2 value;
end;
run;