BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
ieva
Pyrite | Level 9
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
Not applicable
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
Pyrite | Level 9
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
Not applicable
ieva

thank you so very much!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 876 views
  • 0 likes
  • 2 in conversation