Programming the statistical procedures from SAS

detect drastic price changes within a time frame

Reply
Super Contributor
Posts: 395

detect drastic price changes within a time frame

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

Respected Advisor
Posts: 3,124

Re: within timeline price changes drastically

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.

Super Contributor
Posts: 395

Re: within timeline price changes drastically

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.

Respected Advisor
Posts: 3,124

Re: within timeline price changes drastically

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

Super Contributor
Posts: 395

Re: within timeline price changes drastically

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

SAS Super FREQ
Posts: 3,306

detect drastic price changes within a time frame

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

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

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

Ask a Question
Discussion stats
  • 5 replies
  • 135 views
  • 0 likes
  • 3 in conversation