Hey all,
I wanted to find the average for the past six months, but was confused. I tried using Proc Means but it didn't work. Any help is appreciated.
I have,
Date Returns Liquidity Volatility
011980 2.3 4.4 2.2
021980 2.2 4.2 2.3
031980
041980
051980
061980
071980
081980
...
...
...
..
112011
So, what i want is new variables, say Return_avg, Liq_avg, Vol_avg where the average is of the six months preceding the date. For instance, for 081980, I would want the average from 021980-071980. For the date 091980, I want average from 031980-081980.
How should I go about this? Any help is apprecaited. Thanks guys.
I stand corrected! I should have read OP's request more carefully. My query should be :
proc sql;
create table want as
select h1.date format=mmyyd., mean(h2.return) as Return_avg,
mean(h2.liquity) as Liq_avg, mean(h2.Volatility) as Vol_av,
count(*) as months
from have as h1 inner join have as h2 on
h2.date between intnx("month",h1.date,-6,"beginning") and
intnx("month",h1.date,-1,"beginning")
group by h1.date;
select * from want;
quit;
Thanks!
PG
Try this one:
data have;
do _n_=1 to 12;
date=(intnx('month','01dec1979'd,_n_,'b'));
return=2+_n_/10;
liquity=4+_n_/10;
Volatility=round((2+_n_/9),.1);
format date mmddyy10.;
output;
end;
run;
proc sql;
create table want as select a.date format=mmyyn.,
mean(b.return) as return_ave,
mean(b.liquity)as liq_ave,
round(mean(b.Volatility),.01) as vol_ave
from have a,have b
where (b.date between intnx('month',a.date,-6,'b')
and intnx('month',a.date,-1,'e'))
group by a.date;
quit;
proc print;run;
obs date ave liq_ave vol_ave
1 021980 2.10 4.10 2.10
2 031980 2.15 4.15 2.15
3 041980 2.20 4.20 2.20
4 051980 2.25 4.25 2.25
5 061980 2.30 4.30 2.32
6 071980 2.35 4.35 2.38
7 081980 2.45 4.45 2.50
8 091980 2.55 4.55 2.62
9 101980 2.65 4.65 2.73
10 111980 2.75 4.75 2.85
11 121980 2.85 4.85 2.95
My original code was so ugly, I have to modify it.
Message was edited by:Linlin
Picking up from Linlin, but simpler :
data have;
do _n_=1 to 18;
date=(intnx('month','01dec1979'd,_n_,'b'));
return=2+_n_/10;
liquity=4+_n_/10;
Volatility=round((2+_n_/9),.1);
format date mmddyy10.;
output;
end;
run;
proc sql;
create table want as
select h1.date format=mmyyd., mean(h2.return) as Return_avg,
mean(h2.liquity) as Liq_avg, mean(h2.Volatility) as Vol_av,
count(*) as months
from have as h1 inner join have as h2 on
h2.date between intnx("month",h1.date,-5,"beginning") and h1.date
group by h1.date;
select * from want;
quit;
-------------
date Return_avg Liq_avg Vol_av months
-----------------------------------------------------
01-1980 2.1 4.1 2.1 1
02-1980 2.15 4.15 2.15 2
03-1980 2.2 4.2 2.2 3
04-1980 2.25 4.25 2.25 4
05-1980 2.3 4.3 2.32 5
06-1980 2.35 4.35 2.383333 6
07-1980 2.45 4.45 2.5 6
08-1980 2.55 4.55 2.616667 6
09-1980 2.65 4.65 2.733333 6
10-1980 2.75 4.75 2.85 6
11-1980 2.85 4.85 2.95 6
12-1980 2.95 4.95 3.05 6
01-1981 3.05 5.05 3.15 6
02-1981 3.15 5.15 3.266667 6
03-1981 3.25 5.25 3.383333 6
04-1981 3.35 5.35 3.5 6
05-1981 3.45 5.45 3.616667 6
06-1981 3.55 5.55 3.733333 6
PG
Hi PG,
I love your code. You used condition "b.date between a.date and intnx('month',a.date,-5,'b')", I thought the OP asked for
"b.date between intnx('month',a.date,-6,'b') and intnx('month',a.date,-1,'e')".
Thanks -Linlin
Always touchy to figure those out. intnx('month',a.date,0,'b') is the first day of this month, that's one month; intnx('month',a.date,-1,'b') is the first day of last month, with this month, that's two months, and so on. So, between intnx('month',a.date,-5,'b') and date covers six months.
I added the count(*) as months to the output as an extra check.
PG
Hi PG,
using the same input dataset, our outputs are different. I printed output starting from 09-1980
output from PG
Return_
date avg Liq_avg Vol_av months
09-1980 2.65 4.65 2.73333 6
10-1980 2.75 4.75 2.85000 6
11-1980 2.85 4.85 2.95000 6
12-1980 2.95 4.95 3.05000 6
01-1981 3.05 5.05 3.15000 6
02-1981 3.15 5.15 3.26667 6
03-1981 3.25 5.25 3.38333 6
04-1981 3.35 5.35 3.50000 6
05-1981 3.45 5.45 3.61667 6
06-1981 3.55 5.55 3.73333 6
output from Linlin
return_
date ave liq_ave vol_ave
091980 2.55 4.55 2.62
101980 2.65 4.65 2.73
111980 2.75 4.75 2.85
121980 2.85 4.85 2.95
011981 2.95 4.95 3.05
021981 3.05 5.05 3.15
031981 3.15 5.15 3.27
041981 3.25 5.25 3.38
051981 3.35 5.35 3.50
061981 3.45 5.45 3.62
I stand corrected! I should have read OP's request more carefully. My query should be :
proc sql;
create table want as
select h1.date format=mmyyd., mean(h2.return) as Return_avg,
mean(h2.liquity) as Liq_avg, mean(h2.Volatility) as Vol_av,
count(*) as months
from have as h1 inner join have as h2 on
h2.date between intnx("month",h1.date,-6,"beginning") and
intnx("month",h1.date,-1,"beginning")
group by h1.date;
select * from want;
quit;
Thanks!
PG
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
If you search the discussion forums with keyword "rolling" you will find a lot of different examples of how to do it.
Below some SQL close to what PG already suggested.
proc sql;
/*create table want as*/
select distinct
a.*
, avg(b.return) as avg6_return format=16.2
, avg(b.liquity) as avg6_liquity format=16.2
, avg(b.Volatility) as avg6_Volatility format=16.2
from have A inner join have B
on b.date between a.date and intnx('month',a.date,-5,'b')
group by a.date
having count(*)=6
;
quit;
Refreshing by Linlin's post here:
I think we could easily have a data step solution, raw data was taken from Linlin's post shamelessly.
data have;
do _n_=1 to 12;
date=(intnx('month','01dec1979'd,_n_,'b'));
return=2+_n_/10;
liquity=4+_n_/10;
Volatility=round((2+_n_/9),.1);
format date mmddyy10.;
output;
end;
run;
data want;
set have;
array _r{0:5} _temporary_;
array _l{0:5} _temporary_;
array _v{0:5} _temporary_;
_r{mod(_n_,6)}=return;
_l{mod(_n_,6)}=liquity;
_v{mod(_n_,6)}=volatility;
m_return=mean(of _r(*));
m_liquity=mean(of _l(*));
m_volatility=mean(of _v(*));
run;
data want1;
set have (firstobs=2 keep=date);
set want1(keep=m:);
run;
proc print;run;
Of course, if you have ETS, life would be a lot easier ( I am actually surprised that nobody even mention proc expand):
proc expand data=have out=want method=none;
convert return=m_return /tranformout=(movavg 6);
convert liquity=m_liquity /tranformout=(movavg 6);
convert Volatility=m_Volatility /tranformout=(movavg 6);
run;
data want1;
set want (firstobs=2 keep=date);
set want (keep=m:);
run;
Haikuo
Hi Haikuo,
That is the reason I asked the question, I spent sometime but didn't figure it out. We are so luck to have smart people like PG to help us. - Linlin
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.