Annual average

[deleted_user]

I need to calculate annual average, ie, the average of today’s value with the value 12 months back. If I have a variable with monthly observations, how can I do that? Please note that I am working with multiple stocks.

An example is given below.

Date Var. Avg.
Jan-08 1
Feb-08 2
Mar-08 3
Apr-08 4
May-08 5
Jun-08 6
Jul-08 7
Aug-08 8
Sep-08 9
Oct-08 10
Nov-08 11
Dec-08 12
Jan-09 13 (1+13)/2

I know that I can do this using lag12 function, but then I am not able to figure out how to reset the first 12 observations for each stock.

Any help would be really appreciated. Have been stuck on this for a while

[ieva]

Not sure if I understand what do you want to calculate. Would it be just one number - value for Jan09 =(1+13)/2 or you want averages for each following month?

[deleted_user]

Ieva

Its for each following month, hope the table below makes it clear.

I can lag "Var." by lag12 and find the required average. But then I also need to reset my first 12 values for each firm because once I lag by 12 then ABC values will overlap with XYZ. This is where I am stuck, 'How do I reset the first 12 values for each firm?'.

Date Firm Var. Avg.
Jan-08 ABC 1 1
Feb-08 ABC 2 2
Mar-08 ABC 3 3
Apr-08 ABC 4 4
May-08 ABC 5 5
Jun-08 ABC 6 6
Jul-08 ABC 7 7
Aug-08 ABC 8 8
Sep-08 ABC 9 9
Oct-08 ABC 10 10
Nov-08 ABC 11 11
Dec-08 ABC 12 12
Jan-09 ABC 13 (1+13)/2
Feb-09 ABC 14 (2+14)/2
Mar-09 ABC 15 (3+15)/2
May-09 ABC 16 (4+16)/2
Jun-09 ABC 17 (5+17)/2
Jul-09 ABC 18 (6+18)/2
Jan-08 XYZ 21 21
Feb-08 XYZ 22 22
Mar-08 XYZ 23 23
Apr-08 XYZ 24 24
May-08 XYZ 25 25
Jun-08 XYZ 26 26
Jul-08 XYZ 27 27
Aug-08 XYZ 28 28
Sep-08 XYZ 29 29
Oct-08 XYZ 30 30
Nov-08 XYZ 31 31
Dec-08 XYZ 32 32
Jan-09 XYZ 33 (21+33)/2
Feb-09 XYZ 34 (22+34)/2
Mar-09 XYZ 35 (23+35)/2
May-09 XYZ 36 (24+36)/2
Jun-09 XYZ 37 (25+37)/2
Jul-09 XYZ 38 (26+38)/2

Message was edited by: ronmama Message was edited by: ronmama

[ieva]

You can try this, maybe that is what you need:

data test;
input Date date9. Firm $ Var Avg_necessary $9.;
format date date9.;
datalines ;
1-Jan-08 ABC 1 1
1-Feb-08 ABC 2 2
1-Mar-08 ABC 3 3
1-Apr-08 ABC 4 4
1-May-08 ABC 5 5
1-Jun-08 ABC 6 6
1-Jul-08 ABC 7 7
1-Aug-08 ABC 8 8
1-Sep-08 ABC 9 9
1-Oct-08 ABC 10 10
1-Nov-08 ABC 11 11
1-Dec-08 ABC 12 12
1-Jan-09 ABC 13 (1+13)/2
1-Feb-09 ABC 14 (2+14)/2
1-Mar-09 ABC 15 (3+15)/2
1-May-09 ABC 16 (4+16)/2
1-Jun-09 ABC 17 (5+17)/2
1-Jul-09 ABC 18 (6+18)/2
1-Jan-08 XYZ 21 21
1-Feb-08 XYZ 22 22
1-Mar-08 XYZ 23 23
1-Apr-08 XYZ 24 24
1-May-08 XYZ 25 25
1-Jun-08 XYZ 26 26
1-Jul-08 XYZ 27 27
1-Aug-08 XYZ 28 28
1-Sep-08 XYZ 29 29
1-Oct-08 XYZ 30 30
1-Nov-08 XYZ 31 31
1-Dec-08 XYZ 32 32
1-Jan-09 XYZ 33 (21+33)/2
1-Feb-09 XYZ 34 (22+34)/2
1-Mar-09 XYZ 35 (23+35)/2
1-May-09 XYZ 36 (24+36)/2
1-Jun-09 XYZ 37 (25+37)/2
1-Jul-09 XYZ 38 (26+38)/2
;
run;

proc sort data=test;
by firm date;
run;

data test2;
set test;
by firm;
if first.firm then counter=0;
counter+1;
lag12=lag12(var);
if counter le 12 then avg=var;
else avg=(var+lag12)/2;
run;

You can drop counter and lag12 from final data of course.

[deleted_user]

ieva

thank you so very much!