Help using Base SAS procedures

Moving Averages in SAS by Symbol

Reply
Contributor
Posts: 24

Moving Averages in SAS by Symbol

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

PROC Star
Posts: 7,363

Re: Moving Averages in SAS by Symbol

Did you look at:

Contributor
Posts: 24

Re: Moving Averages in SAS by Symbol

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

identifieryearvalue
A19984
A19993
A20002
A20011
A20024
A20036
A20048
A20050
A20066
A20074
A20083
A20091
A20102
B19983
B19996
B20009
B20010
B20027
B20034

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:

identifieryearvalue3-year average
A19984.
A19993.
A200023
A200112
A200242.333333333
A200363.666666667
A200486
A200504.666666667
A200664.666666667
A200743.333333333
A200834.333333333
A200912.666666667
A201022
B19983.
B19996.
B200096
B200105
B200275.333333333
B20033.5
B200424.5
B200574.5
B200644.333333333
B200796.666666667
B200856
B200966.666666667
B201034.666666667

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

Thanks in advance.

Respected Advisor
Posts: 3,899

Re: Moving Averages in SAS by Symbol

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

Super Contributor
Posts: 1,636

Re: Moving Averages in SAS by Symbol

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;

Ask a Question
Discussion stats
  • 4 replies
  • 197 views
  • 0 likes
  • 4 in conversation