Finding average for the past six months

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Finding average for the past six months

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.


Accepted Solutions
Solution
‎04-14-2012 10:07 PM
Respected Advisor
Posts: 4,651

Re: Finding average for the past six months

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

PG

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: Finding average for the past six months

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

Respected Advisor
Posts: 4,651

Re: Finding average for the past six months

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

PG
Super Contributor
Posts: 1,636

Re: Finding average for the past six months

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

Respected Advisor
Posts: 4,651

Re: Finding average for the past six months

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

PG
Super Contributor
Posts: 1,636

Re: Finding average for the past six months

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

Solution
‎04-14-2012 10:07 PM
Respected Advisor
Posts: 4,651

Re: Finding average for the past six months

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

PG
Contributor
Posts: 39

Re: Finding average for the past six months

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                                                  

Respected Advisor
Posts: 3,894

Re: Finding average for the past six months

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;

Respected Advisor
Posts: 3,124

Re: Finding average for the past six months

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=mSmiley Happy;

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=mSmiley Happy;

run;

Haikuo

Super Contributor
Posts: 1,636

Re: Finding average for the past six months

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 

☑ This topic is SOLVED.

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

Discussion stats
  • 10 replies
  • 953 views
  • 6 likes
  • 6 in conversation