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..
03-06-2012 08:48 AM
Well, if you have SAS/ETS, then proc expand seems to suit your need:
input a @@;
1 2 3 4 3 5 6 2 10 5 6 1
proc expand data=have out=want;
convert a=b/ transformout=(MOVRANGE 3);
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'.
Edit: you mentioned lag(), which is not a bad idea in my opinion:
Well, this is the case for 3, if 7 bothers you, you can always use array to cut off some coding labor.
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.
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?
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...
... it could gradually go up to Price= 600 which is fine...
And another Sample is
03-06-2012 09:01 AM
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:
There is also the MACONTROL procedure for creating moving average control charts