Calculate average with stange result

Accepted Solution Solved
Reply
Contributor
Posts: 40
Accepted Solution

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?

CUSIPEndwindowcValue2r2
378331030-Dec-880.56250.5625
378331031-Jan-890.56160.5616
378331028-Feb-890.51130.5113
378331031-Mar-890.51930.5193
378331028-Apr-890.50780.5078
378331031-May-890.51520.5152
378331030-Jun-890.50770.5077
378331031-Jul-890.50310.5031
378331031-Aug-890.44420.4442
378331029-Sep-890.4310.431
378331031-Oct-890.41830.4183
378331030-Nov-890.41540.4154
378331029-Dec-890.41650.4165
378331031-Jan-900.38330.3833
378331028-Feb-900.30590.3059
378331030-Mar-900.29680.2968
378331030-Apr-900.30560.3056
378331031-May-900.36280.3628
378331029-Jun-900.3560.356
378331031-Jul-900.34860.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; 

 

CUSIPEndwindowr2_avgmonths
3783310Mar-890.466133
3783310Apr-890.46716
3783310May-890.463529
3783310Jun-890.4638912
3783310Jul-890.4591215
3783310Aug-890.4582718
3783310Sep-890.4574221
3783310Oct-890.4569824
3783310Nov-890.4526727
3783310Dec-890.4491430
3783310Jan-900.4438830
3783310Feb-900.4392330
3783310Mar-900.4365630
3783310Apr-900.431330
3783310May-900.4189330
3783310Jun-900.4043930
3783310Jul-900.3908330

 

Thank you so much,

Ha                                                  


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

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

View solution in original post


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

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 1 reply
  • 190 views
  • 0 likes
  • 2 in conversation