## Average score for 3 consecutive years

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

 Company Code Year Market to Book value 3 years average Value PS 5001 2004 0.54 0.64 PS 5001 2005 0.63 0.75 PS 5001 2006 0.75 0.78 PS 5001 2007 0.88 0.71 PS 5001 2008 0.72 0.60 PS 5001 2009 0.53 0.57 PS 5001 2010 0.54 PS 5001 2011 0.63 Jo 5002 2004 0.74 0.783 Jo 5002 2005 0.84 0.687 Jo 5002 2006 0.77 Jo 5002 2007 0.45 sm 5003 2005 0.63 0.7 sm 5003 2006 0.77 0.6 sm 5003 2007 0.63 0.6 sm 5003 2008 0.52 sm 5003 2009 0.53
PROC Star
Posts: 8,167

## 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;

Discussion stats