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..
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.
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.
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
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
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
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!ANOVA, or Analysis Of Variance, is used to compare the averages or means of two or more populations to better understand how they differ. Watch this tutorial for more.
Find more tutorials on the SAS Users YouTube channel.