BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

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

5 REPLIES 5
Haikuo
Onyx | Level 15

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
Quartz | Level 8

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
Onyx | Level 15

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
Quartz | Level 8

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

Rick_SAS
SAS Super FREQ

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is ANOVA?

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.

Discussion stats
  • 5 replies
  • 1346 views
  • 0 likes
  • 3 in conversation