detect drastic price changes within a time frame

podarum · Posted 03-06-2012 08:33 AM

Hi, I know there's somebody out there that knows how this can be coded in SAS. What I'm looking for is a way to detect drastic price changes (upward or downward) within a 7 month period... so I have a large time series data (segregated by a couple of BY groups) that can have these drastic price changes, for example price from one month jumps up by 70% and then 4 months later drops by 40% or so... This spike is what I'd like to find. I've done something with lags, and it seems to work 'Ok', but I know there is some coding out there that either someone tried, or makes better sense.. thanks all..

Haikuo · Posted 03-06-2012 08:48 AM

Well, if you have SAS/ETS, then proc expand seems to suit your need:

data have;

input a @@;

cards;

1 2 3 4 3 5 6 2 10 5 6 1

;

proc expand data=have out=want;

convert a=b/ transformout=(MOVRANGE 3);

run;

Above code is showing the how to obtain 'backward moving range' of 3 records. for your case, it will be 7 if your monthly data is outlined by rows. proc expand also supports 'forward moving range'.

Regards,

Haikuo

Edit: you mentioned lag(), which is not a bad idea in my opinion:

data want_lag;

set have;

range=range(a,lag(a),lag2(a));

run;

Well, this is the case for 3, if 7 bothers you, you can always use array to cut off some coding labor.

podarum · Posted 03-06-2012 08:54 AM

Thanks Haikuo, but I've tried that before.. the problem is I'm looking for spikes that could be 2 months long or 7 months long, and the data is already seasonal and averaged out... it's these certain spikes that may pop up in certain months and in certain series. As I need to identify the 'Bad ares' first, and then I can apply the proc expand to them.

Haikuo · Posted 03-06-2012 09:04 AM

So your data is not monthly basis, rather, they are averaged on seasonal basis? And you are trying to identify certain 'spiking' month? Can you post just a few line of your data?

Regards,

Haikuo

podarum · Posted 03-06-2012 09:14 AM

It's monthly based, but it is on a 6-month rolling average... but where there is a low count, the spikes happen... here's some examples...

Date Price

2003.03 571

2003.04 483

2003.05 571

2003.06 428

2003.07 428

2003.08 395

2003.09 334

2003.10 242

2003.11 243

2003.12 251

2004.01 267

2004.02 244

... it could gradually go up to Price= 600 which is fine...

And another Sample is

Date Price

2002.11 267

2002.12 278

2003.01 330

2003.02 390