Solved
Contributor
Posts: 22

# 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.

Accepted Solutions
Solution
‎04-14-2012 10:07 PM
Posts: 5,333

## 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

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

Posts: 5,333

## 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

Posts: 5,333

## 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
Posts: 5,333

## 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: 44

## 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?

 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

Posts: 4,466

## 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;

Posts: 3,162

## 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=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

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.