topic How to do a cumulative sum starting from a specific date ? in SAS Programming

How to do a cumulative sum starting from a specific date ?

Midi — Thu, 14 Nov 2019 16:40:08 GMT

Hello Everyone , I have this Dataset :

Index	Diff_Day	Price_Month	Price_Day
L1	-2	12,3	45
L1	-1	1234	102
L1	0	154	745
L1	1	654	632
L1	2	54	12
L2	-2	554	302
L2	-1	35	123
L2	0	543	1023
L2	1	545	3012
L2	2	421	56
L3	-2	213	878
L3	-1	3253	353
L3	0	354	213
L3	1	154	3213
L3	2	1230	3021

And i want to do a cumulative sum for price month and price day , but starting from diff_day 0 , like my output would be like this (here , i'm showing the cumulative sum for price month , but i want to do the same thing for price day):

Index	Diff_Day	Price_Month	Price_Day	CumSum_Price_Month
L1	-2	12,3	45	12,3
L1	-1	1234	102	1234
L1	0	154	745	154
L1	1	654	632	808
L1	2	54	12	862
L2	-2	554	302	554
L2	-1	35	123	35
L2	0	543	1023	543
L2	1	545	3012	1088
L2	2	421	56	1509
L3	-2	213	878	213
L3	-1	3253	353	3253
L3	0	354	213	354
L3	1	154	3213	508
L3	2	1230	3021	1738

Any suggestion on how to do that would much appreciated , thank you.

Re: How to do a cumulative sum starting from a specific date ?

PeterClemmensen — Thu, 14 Nov 2019 16:52:48 GMT

data have;
input Index $ Diff_Day Price_Month Price_Day;
datalines;
L1 -2 12.3 45   
L1 -1 1234 102  
L1  0 154  745  
L1  1 654  632  
L1  2 54   12   
L2 -2 554  302  
L2 -1 35   123  
L2  0 543  1023 
L2  1 545  3012 
L2  2 421  56   
L3 -2 213  878  
L3 -1 3253 353  
L3  0 354  213  
L3  1 154  3213 
L3  2 1230 3021 
;

data want;
   do until (last.Index);
      set have;
      by Index;
      if Diff_Day <= 0 then do;
         CumSum_Price_Month = Price_Month;
         CumSum_Price_Day   = Price_Day;
      end;
      else do;
         CumSum_Price_Month + Price_Month;
         CumSum_Price_Day + Price_Day;
      end;
      output;
   end;
run;

Result:

Index Diff_Day Price_Month Price_Day CumSum_Price_Month CumSum_Price_Day 
L1    -2       12.3        45        12.3               45 
L1    -1       1234.0      102       1234.0             102 
L1     0       154.0       745       154.0              745 
L1     1       654.0       632       808.0              1377 
L1     2       54.0        12        862.0              1389 
L2    -2       554.0       302       554.0              302 
L2    -1       35.0        123       35.0               123 
L2     0       543.0       1023      543.0              1023 
L2     1       545.0       3012      1088.0             4035 
L2     2       421.0       56        1509.0             4091 
L3    -2       213.0       878       213.0              878 
L3    -1       3253.0      353       3253.0             353 
L3     0       354.0       213       354.0              213 
L3     1       154.0       3213      508.0              3426 
L3     2       1230.0      3021      1738.0             6447

Re: How to do a cumulative sum starting from a specific date ?

novinosrin — Thu, 14 Nov 2019 17:09:15 GMT



data have;
input Index $	Diff_Day 	Price_Month	Price_Day;
cards;
L1	-2	12.3	45
L1	-1	1234	102
L1	0	154	745
L1	1	654	632
L1	2	54	12
L2	-2	554	302
L2	-1	35	123
L2	0	543	1023
L2	1	545	3012
L2	2	421	56
L3	-2	213	878
L3	-1	3253	353
L3	0	354	213
L3	1	154	3213
L3	2	1230	3021
;

data want;
 set have;
 by index;
 if first.index or Diff_Day<=0 then do;
  CumSum_Price_Month=Price_Month;
  cumPrice_day=Price_Day;
  return;
 end;
  CumSum_Price_Month+Price_Month;
  cumPrice_day+Price_Day;
run;