Solved
Contributor
Posts: 44

# Calculate average with stange result

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

Accepted Solutions
Solution
‎10-27-2016 06:05 AM
Super User
Posts: 10,787

## Re: Calculate average with stange result

[ Edited ]

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

All Replies
Solution
‎10-27-2016 06:05 AM
Super User
Posts: 10,787

## Re: Calculate average with stange result

[ Edited ]

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

☑ This topic is solved.

Discussion stats