Calculating the mean change in scores between dates

[danielhu]

Hi, I will need to calculate the (absolute) mean change in scores between years for the following example. There are some firm that may only have scores for certain years. This is really tricky, hope i can render some help here.

Year	Firm	Scores
2005	A	5
2005	B	8
2006	A	7
2006	C	4
2007	A	8
2007	B	10
2007	D	8
2007	E	3

[Reeza]

What is your expected output?

[Steelers_In_DC]

I have the time right now so I'm taking a guess at what you are looking for.  An example of what you are looking for would be helpful. Try this:

data have;

infile cards;

input Year Firm $ Scores;

cards;

2005 A 5

2005 B 8

2006 A 7

2006 C 4

2007 A 8

2007 B 10

2007 D 8

2007 E 3

;

run;

proc sql;

create table prep as

select distinct year, sum(scores) as sum_scores

from have

group by year

order by year;

data start(drop=l_sum_scores);

set prep;

by year;

l_sum_scores = lag(sum_scores);

diff_scores = abs(l_sum_scores - sum_scores);

run;

proc sql;

create table want as

select *,mean(diff_scores) as mean_scores

from start;