BookmarkSubscribeRSS Feed
RazzleBayker
Calcite | Level 5

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

4 REPLIES 4
art297
Opal | Level 21

Did you look at:

RazzleBayker
Calcite | Level 5

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.

Patrick
Opal | Level 21

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;

Linlin
Lapis Lazuli | Level 10

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 2068 views
  • 0 likes
  • 4 in conversation