Average score for 3 consecutive years

Reply
Occasional Contributor
Posts: 5

Average score for 3 consecutive years

Hi there,

I have provide a sample of my data as below.   My data ranges from  years  2004 to 2011 for each company.

I want to know how to calculate the average market to book value for 3 consecutive years for a particular company.  For example,

Year 2004 Company PS (Average value)             =  (0.54+0.63+0.75)

Figures take from the data below                                        3

                                                                      =0.64

CompanyCodeYear Market to Book value3 years average Value
PS500120040.540.64
PS500120050.630.75
PS500120060.750.78
PS500120070.880.71
PS500120080.720.60
PS500120090.530.57
PS500120100.54
PS500120110.63
Jo500220040.740.783
Jo500220050.840.687
Jo500220060.77
Jo500220070.45
sm500320050.630.7
sm500320060.770.6
sm500320070.630.6
sm500320080.52
sm500320090.53
PROC Star
Posts: 7,356

Re: Average score for 3 consecutive years

If you don't have any missing years, then you can use the fifo method suggested by in his presentation at:

http://support.sas.com/community/events/sastalks/presentations/Loops_and_Arrays_12april.pdf

An implementation of that method, given your data, might be:

data have;

  input Company $ Code Year Market_value;

  cards;

PS 5001 2004 0.54

PS 5001 2005 0.63

PS 5001 2006 0.75

PS 5001 2007 0.88

PS 5001 2008 0.72

PS 5001 2009 0.53

PS 5001 2010 0.54

PS 5001 2011 0.63

Jo 5002 2004 0.74

Jo 5002 2005 0.84

Jo 5002 2006 0.77

Jo 5002 2007 0.45

sm 5003 2005 0.63

sm 5003 2006 0.77

sm 5003 2007 0.63

sm 5003 2008 0.52

sm 5003 2009 0.53

;

proc sort data=have out=want;

  by company descending year;

run;

data want (drop=counter);

  set want;

  by company;

  retain counter .;

  array stack {0:2} _temporary_;

  if first.company then do;

    call missing(of stack{*});

    counter=0;

  end;

  counter+1;

  stack{mod(counter,3)} = Market_value;

  if counter gt 2 then avg3year = mean(of stack{*});

run;

proc sort data=want;

  by company year;

run;

Ask a Question
Discussion stats
  • 1 reply
  • 163 views
  • 0 likes
  • 2 in conversation