topic detect drastic price changes within a time frame in Statistical Procedures

detect drastic price changes within a time frame

podarum — Tue, 06 Mar 2012 13:33:20 GMT

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..

Re: within timeline price changes drastically

Haikuo — Tue, 06 Mar 2012 13:48:59 GMT

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.

Re: within timeline price changes drastically

podarum — Tue, 06 Mar 2012 13:54:08 GMT

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.

detect drastic price changes within a time frame

Rick_SAS — Tue, 06 Mar 2012 14:01:21 GMT

This isn't really my area, but if you have a stationary process you can use SAS/QC control charts to monitor "normal" versus "abnormal" variation. The SHEWHART procedure has a wide variety of charts for monitoring processes. For example, see these two examples:

http://support.sas.com/documentation/cdl/en/qcug/63964/HTML/default/viewer.htm#qcug_shewhart_a0000003904.htm

http://support.sas.com/documentation/cdl/en/qcug/63964/HTML/default/viewer.htm#qcug_shewhart_a0000003932.htm

There is also the MACONTROL procedure for creating moving average control charts

http://support.sas.com/documentation/cdl/en/qcug/63964/HTML/default/viewer.htm#qcug_macontrol_a0000000333.htm

Re: within timeline price changes drastically

Haikuo — Tue, 06 Mar 2012 14:04:17 GMT

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

Re: within timeline price changes drastically

podarum — Tue, 06 Mar 2012 14:14:03 GMT

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

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