topic Re: Moving Averages in SAS by Symbol in SAS Procedures

Moving Averages in SAS by Symbol

RazzleBayker — Fri, 01 Feb 2013 23:36:36 GMT

pHi guys,

I'm working on a project where I need to calculate a moving average (3 lags) prices (variable name "PRICE") by stock ticker symbol (valiable name "TICKER"). Can someone please give me a code that I can use to do this in SAS?

Thanks in advance.

Regards,

Razzle

Re: Moving Averages in SAS by Symbol

art297 — Fri, 01 Feb 2013 23:41:15 GMT

Did you look at:

Re: Moving Averages in SAS by Symbol

RazzleBayker — Sat, 02 Feb 2013 01:24:44 GMT

Yeah, unfortunately it's a slightly different procedure. My data looks like this:

identifier	year	value
A	1998	4
A	1999	3
A	2000	2
A	2001	1
A	2002	4
A	2003	6
A	2004	8
A	2005	0
A	2006	6
A	2007	4
A	2008	3
A	2009	1
A	2010	2
B	1998	3
B	1999	6
B	2000	9
B	2001	0
B	2002	7
B	2003	4

I need to calculate a moving average (3 lags; 3 years in this case) starting with the first year of each individual identifier (first column). So basically the result for this example (above) that I'm looking for is shown in the 4th column:

identifier	year	value	3-year average
A	1998	4	.
A	1999	3	.
A	2000	2	3
A	2001	1	2
A	2002	4	2.333333333
A	2003	6	3.666666667
A	2004	8	6
A	2005	0	4.666666667
A	2006	6	4.666666667
A	2007	4	3.333333333
A	2008	3	4.333333333
A	2009	1	2.666666667
A	2010	2	2
B	1998	3	.
B	1999	6	.
B	2000	9	6
B	2001	0	5
B	2002	7	5.333333333
B	2003		3.5
B	2004	2	4.5
B	2005	7	4.5
B	2006	4	4.333333333
B	2007	9	6.666666667
B	2008	5	6
B	2009	6	6.666666667
B	2010	3	4.666666667

Is there a simple code I can use in SAS to do this?

Thanks in advance.

Re: Moving Averages in SAS by Symbol

Patrick — Sat, 02 Feb 2013 02:23:49 GMT

Assuming there are no missing years and no missing values as shown in your demo data below code should do.

data have;
input identifier:$1. year value;
datalines;
A 1998 4
A 1999 3
A 2000 2
A 2001 1
A 2002 4
A 2003 6
A 2004 8
A 2005 0
A 2006 6
A 2007 4
A 2008 3
A 2009 1
A 2010 2
B 1998 3
B 1999 6
B 2000 9
B 2001 0
B 2002 7
B 2003 4
;
run;

data want(drop=_:);
set have;
by identifier;

if first.identifier then
_i=1;
else _i+1;
Year3_Avg=ifn(_i>=3,mean(value,lag(value),lag2(value)),.);
run;

Re: Moving Averages in SAS by Symbol

Linlin — Sat, 02 Feb 2013 02:50:42 GMT

by sql:

data have;

input identifier $ year value;

cards;

A 1998 4

A 1999 3

A 2000 2

A 2001 1

A 2002 4

A 2003 6

A 2004 8

A 2005 0

A 2006 6

A 2007 4

A 2008 3

A 2009 1

A 2010 2

B 1998 3

B 1999 6

B 2000 9

B 2001 0

B 2002 7

B 2003 4

;

proc sql;

create table temp

as select a.identifier, a.year,b.value

from have as a , have as b

where a.identifier=b.identifier and (b.year between a.year-3 and a.year);

create table want as

select identifier,year,mean(value) as M_value

from temp

group by identifier,year;

quit;