Finding average for the past six months

[sharmas]

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.

[Linlin]

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

[PGStats]

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

[Linlin]

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

[PGStats]

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

[Linlin]

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

[PGStats · Accepted solution]

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

[yotsuba88]

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                                                  

[Patrick]

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;

[Haikuo]

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

[Linlin]

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