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
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
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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.