topic Re: How to calculate the cumulative sum by a group over the previous months in SAS Programming

How to calculate the cumulative sum by a group over the previous months

fongdo — Tue, 02 Feb 2021 03:55:35 GMT

Hello,

I would like to calculate the cumulative return on the stock over the previous 11 months starting 2 months ago, i.e., the cumulative return from month t-12 to month t-2. The following data give some ideas about what I want:

STOCK	Year	Month	Return	WANT
A	2010	1	10	.
A	2010	2	15	.
A	2010	3	20	.
A	2010	4	30	.
A	2010	5	25	.
A	2010	6	20	.
A	2010	7	40	.
A	2010	8	15	.
A	2010	9	20	.
A	2010	10	10	.
A	2010	11	10	.
A	2010	12	20	.
A	2011	1	5	215
A	2011	2	10	225
A	2011	3	15	215
A	2011	4	15	205
A	2011	5	20	190
A	2011	6	25	180
A	2011	7	30	180
A	2011	8	20	165
A	2011	9	10	180
A	2011	10	10	180
A	2011	11	20	180
A	2011	12	10	180
B	2010	1	90	.
B	2010	2	40	.
B	2010	3	100	.
B	2010	4	50	.
B	2010	5	80	.
B	2010	6	20	.
B	2010	7	150	.
B	2010	8	30	.
B	2010	9	80	.
B	2010	10	20	.
B	2010	11	10	.
B	2010	12	60	.
B	2011	1	20	670
B	2011	2	15	640
B	2011	3	20	620
B	2011	4	30	535
B	2011	5	10	505
B	2011	6	70	455
B	2011	7	60	445
B	2011	8	120	365
B	2011	9	80	395
B	2011	10	100	435
B	2011	11	80	495
B	2011	12	20	585

I want a variable "WANT" which is the cumulative sum of "Return" by STOCK. For example, the cumulative return of stock A year 2011 month 1 = the cumulative return from the year 2010 month 1 to the year 2010 month 11.

I found some SAS codes from the SAS community, but those codes are used to calculate the cumulative sum by the groups. I tried to modify and test those codes, but they did not work correctly. I wonder if anyone can help me a little bit. Thank you very much for your help!

Cheers,

fongdo

Re: How to calculate the cumulative sum by a group over the previous months

ChrisNZ — Tue, 02 Feb 2021 03:33:20 GMT

Please provide some sample data we can use.

Re: How to calculate the cumulative sum by a group over the previous months

ChrisNZ — Tue, 02 Feb 2021 03:41:49 GMT

Something like this should work:

data WANT;
  set HAVE;
  WANT = (lag12(STOCK)=STOCK)
       * (lag2(RETURN)+lag3(RETURN)+lag4(RETURN)+lag5(RETURN)+lag6(RETURN)+lag7(RETURN)+
          lag8(RETURN)+lag9(RETURN)+lag10(RETURN)+lag11(RETURN)+lag12(RETURN) );
run;

Re: How to calculate the cumulative sum by a group over the previous months

ChrisNZ — Tue, 02 Feb 2021 03:44:50 GMT

or, if you want missing values:

data WANT; set HAVE; WANT = ifn( lag12(STOCK)^=STOCK, ., lag2(RETURN)+lag3(RETURN)+lag4(RETURN)+lag5(RETURN)+lag6(RETURN)+lag7(RETURN) +lag8(RETURN)+lag9(RETURN)+lag10(RETURN)+lag11(RETURN)+lag12(RETURN) ); run;

Re: How to calculate the cumulative sum by a group over the previous months

fongdo — Tue, 02 Feb 2021 04:17:00 GMT

Many thanks @ChrisNZ. Your code works well.