topic Re: Finding average for the past six months in SAS Programming

Finding average for the past six months

sharmas — Sat, 14 Apr 2012 18:01:44 GMT

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.

Re: Finding average for the past six months

Linlin — Sat, 14 Apr 2012 21:55:29 GMT

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

Re: Finding average for the past six months

PGStats — Sat, 14 Apr 2012 22:22:42 GMT

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

Re: Finding average for the past six months

Patrick — Sat, 14 Apr 2012 23:18:56 GMT

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;

Re: Finding average for the past six months

Linlin — Sun, 15 Apr 2012 00:01:50 GMT

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

Re: Finding average for the past six months

PGStats — Sun, 15 Apr 2012 01:00:47 GMT

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.

Re: Finding average for the past six months

Linlin — Sun, 15 Apr 2012 01:32:34 GMT

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

Re: Finding average for the past six months

PGStats — Sun, 15 Apr 2012 02:07:24 GMT

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!

Re: Finding average for the past six months

Haikuo — Mon, 16 Apr 2012 17:48:14 GMT

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

Re: Finding average for the past six months

Linlin — Mon, 16 Apr 2012 18:07:55 GMT

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

Re: Finding average for the past six months

yotsuba88 — Fri, 21 Oct 2016 04:56:28 GMT

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,