Calculate average with stange result

[yotsuba88]

Hi all,

I have data (Rsquare2) here and want to calculate r2_avg over past 12 months except the most recent month (t-12 to t-2). Then I tried to use your code but the result is strange. Could you pleas help me with this?

CUSIP	Endwindow	cValue2	r2
3783310	30-Dec-88	0.5625	0.5625
3783310	31-Jan-89	0.5616	0.5616
3783310	28-Feb-89	0.5113	0.5113
3783310	31-Mar-89	0.5193	0.5193
3783310	28-Apr-89	0.5078	0.5078
3783310	31-May-89	0.5152	0.5152
3783310	30-Jun-89	0.5077	0.5077
3783310	31-Jul-89	0.5031	0.5031
3783310	31-Aug-89	0.4442	0.4442
3783310	29-Sep-89	0.431	0.431
3783310	31-Oct-89	0.4183	0.4183
3783310	30-Nov-89	0.4154	0.4154
3783310	29-Dec-89	0.4165	0.4165
3783310	31-Jan-90	0.3833	0.3833
3783310	28-Feb-90	0.3059	0.3059
3783310	30-Mar-90	0.2968	0.2968
3783310	30-Apr-90	0.3056	0.3056
3783310	31-May-90	0.3628	0.3628
3783310	29-Jun-90	0.356	0.356
3783310	31-Jul-90	0.3486	0.3486

proc sql;
create table want as
select t1. cusip, t1.endwindow format=mmyyd., mean(t2.r2) as r2_avg,
count(*) as months
from Rsquare2 as t1 inner join Rsquare2 as t2 on
t2.endwindow between intnx("month", t1.endwindow,-12,"beginning") and
intnx("month", t1.endwindow,-2,"beginning")
group by t1.cusip, t1.endwindow ;
select * from want;
quit; 

CUSIP	Endwindow	r2_avg	months
3783310	Mar-89	0.46613	3
3783310	Apr-89	0.4671	6
3783310	May-89	0.46352	9
3783310	Jun-89	0.46389	12
3783310	Jul-89	0.45912	15
3783310	Aug-89	0.45827	18
3783310	Sep-89	0.45742	21
3783310	Oct-89	0.45698	24
3783310	Nov-89	0.45267	27
3783310	Dec-89	0.44914	30
3783310	Jan-90	0.44388	30
3783310	Feb-90	0.43923	30
3783310	Mar-90	0.43656	30
3783310	Apr-90	0.4313	30
3783310	May-90	0.41893	30
3783310	Jun-90	0.40439	30
3783310	Jul-90	0.39083	30

Thank you so much,

Ha                                                  

[Ksharp · Accepted solution]

Your day of each row is not the same, suggest make a index variable and

n between n-12 and a.n-2 

CUSIP	Endwindow	cValue2	N
3783310	30-Dec-88	0.5625	1
3783310	31-Jan-89	0.5616	2

 ...................

OR make a new varible which have the same day.

CUSIP

Endwindow

 32342      01-Dec-88

 32342      01-Jan-89