proc sql;
create table capm3 as
select *,
(select mean(rmrf) from capm2
where cusip=a.cusip
and (intnx('year', a.Date, -30, 'b') le Date le a.Date))
as riskpremium format=dollar8.2
from capm2 as a;
quit; Hello all, I am working on dealing with a big number of observations more than 40mililions. What I now want to do is to calculate the past 30-year annual average of stock returns. Conceptually, I have to calculate moving averages of the last 30-year period of each observation and then have to annualize each of them. Before doing the annualization part, I am facing the big challenge of calculating moving average part. What I have now are monthly stock returns. To be as doing correctly as possible, I should use (intnx('month', a.Date, -359, 'b') le Date le a.Date)) instead of using 'year' and -30. However, what I rather would like to ask for your help is not this part. Since I am working on more than 40mils observations, the code I wrote above takes long hours to process, so far more than 5 hours but still processing.... I think this is not the right way to handle it and have been thinking of something else. A very similar way and conceptually the same way I first did is like using PROC SQL and identifying the interval like proc sql; create table WANT as select *,(select mean(rmfr) from HAVE where YYMM between a.YYMM-360 and a.fyear-1 and gvkey=a.gvkey) as WHATIWANT from HAVE as a; quit; Yeah.. you know this took a long long long time as well. It seems that it is common in finance and accounting studies to calculate the so-called "market risk premium" using the difference between the risk-free rate and whatever marker rate over the last 20 or 30 years before the time of observation, so I guess there must be a proper way to do so other than what I have tried so far. Hope kind ones of you share your wisdom with a rookie student. Thank you 🙂
... View more
