Hello - I agree with @LinusH that it is hard to give advice - however I thought it would be useful to share that SAS provides ACCESS engines to both Sybase and FAME. For access Sybase check out: http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002299547.htm For access to FAME data check out: http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_sasefame_sect001.htm Thanks, Udo ... View more

Hello - PROC ESM for example features the END option of the ID statement as well. It might be worthwhile to try and run it on your data without replacing the newly introduced missing values at the end of the series. Example: proc esm data=sashelp.workers out=_null_ outfor=want lead=12 plot=forecasts; id date interval=month start="01JAN76"d end="31DEC1983"d; forecast electric masonry / model=winters; run; *Note that I'm introducing more than a year of missing values at the end of the series - the multistep forecast handles this pretty well with my test data; Alternatively you could consider a 2 step process: first run TIMESERIES to make sure that all series end at the same date and then impute the missing values with some more elaborate technique which you have in mind. Thanks, Udo ... View more

Hello - Have a look at the START and END options of the ID statement of PROC TIMESERIES. Combined with the SETMISSING statement you can force all series to start and end at the same data and impute the missing values accordingly if needed. Example (will work with BY variables, too): proc timeseries data=sashelp.workers out=want; id date interval=month start="01JAN76"d end="31DEC1982"d setmissing=mean; var electric masonry; run; Thanks, Udo ... View more

Hello - The example code and data you have shared seem not to illustrate the problem you were reporting. Here is my interpretation of your data and code - everything seems to work as expected. Thanks, Udo data have; input Store $  Date Price Product $; informat date yymmn6.; format date date10.; datalines; A  200001           234                A1 A  200002           246                A1 A  200003           258                A1 B  200001           564                B2 B  200002           534                B2 B  200003           533                B2 B  200001           345                B1 B  200002           354                B1 C  200503           356                C2 C  200504           367                C2 C  200505           359                C2 run; proc sort data=have;by store product;run; proc expand data=have out=want to=MONTH; by Store product; convert Price  / method=JOIN; id Date; run; ... View more

Hello - Many thanks for sharing your data examples. Needless to state that this is a challenging exercise, also due to the limited amount of history and the different types of "spikes". We are currently working on a new procedure which should be useful for tackling these kind of challenges, which is not released yet. Basically it will provide access to "time series segmentation" methods, which will allow you to separate a series into a major and minor series. As an illustration: Unfortunately this won't help you today, as this procedure has not made its way to the production release, yet. I thought maybe some other smoothing techniques might be useful. So I ran PROC LOESS on your data - again with mixed results, but it might give you some idea on how to proceed. I'm not exactly an expert in using this procedure, but here it goes (I combined all your examples in one data set and used SAMPLE as a series indicator): proc loess data=test;    model price=dat /select=AICC;    by sample; run; For the same sample as above LOESS creates the following graph: and so you could filter out point which have a high residual. Unfortunately this approach does not work well if the spikes are at the beginning or end of the series. Not sure if this is really helpful - but I thought I share it anyway. Thanks, Udo ... View more

Would you be able to share some example data with me? Thanks! Udo ... View more

Hello - Some additional thoughts on my response (for what it's worth): the first challenge I see is to identify an appropriate forecasting model, to avoid filtering out a "signal". If your data is seasonal then spikes can represent a pattern of course, if your data is trending upwards (or downwards) - as it seems to be the case - you will need to use a trend model. Maybe you will need a model which allows for both trend and seasonality. ESM provides you with access to all of these models. If you would have access to HPF as well, then your could use PROC HPF to do a best pick approach. Note the ESM models consider a local trend instead of a global trend - for a nice overview see: http://www.sas.com/reg/wp/corp/3478 The signal to noise ratio is also important to figure out the right value for how many STD to consider. If your data is fairly noisy, then higher values of STD will make more sense, as otherwise you will be filtering data which are not really extreme values. The examples you provided seem to suggest that your data is not very noisy. It might also be worthwhile to consider to difference your data - so instead of the original series you will try to analyze the series of first differences. In this case you should be able to detect "huge" changes in the data even better. Example: data air; set sashelp.air; if date="01JAN1954"d then air=air+200; air2=dif(air); run; proc sgplot data=air; series x=date y=air2; run; Thanks, Udo ... View more

Hello - One approach which you might want to consider is to winsorize your data using ESM for example. Here is an example: *create some test data by adding some extreme spike; data air; set sashelp.air; if date="01JAN1954"d then air=air+200; run; *plot the modified series; proc sgplot data=air; series x=date y=air; run; *run ESM with WINTERS method - since the data is seasonal and trending upwards, in your case you might have to use a different technique, such as SIMPLE; proc esm data=air out=_null_ outfor=want lead=0; id date interval=month; forecast air / method=winters; run; *replace values which have a bad fit with the ESM forecast - of course you will need to find out what flags "bad fit" in your context; data want; set want(rename=actual=air drop=_name_ upper lower); if abs(error) ge 1*std and air ne 0 then air=predict; drop error std predict; run; *plot the result; proc sgplot data=want; series x=date y=air; run; Instead of using the prediction of ESM you could also use other values such as the series median, or the previous value, etc. In this case you could set the values with bad fit to missing and use TIMESERIES to replace the missing values in a 3rd step. Hope this makes sense, Udo ... View more

Hello - You might be interested in this SAS Global Forum paper: http://www.lexjansen.com/wuss/2004/posters/c_post_alternative_seasonali.pdf, featuring PROC X12. In addition this example might be useful: http://support.sas.com/rnd/app/da/new/801ce/ets/chap5/sect21.htm Thanks, Udo ... View more

Hello - You might consider reading all variable names into a macro variable and then run X11. As an example (I'm not filtering out the DATE variable I know - but it might be a start): %macro lst(dsn);    %global x;    %let x=;    %let dsid=%sysfunc(open(&dsn));    %let cnt=%sysfunc(attrn(&dsid,nvars));    %do i = 1 %to &cnt;       %let x=&x %sysfunc(varname(&dsid,&i));    %end;    %let rc=%sysfunc(close(&dsid)); %mend lst; %lst(sashelp.workers) proc x11 data=sashelp.workers noprint;    monthly date=date;    var &x;    tables b1 d11;    output out=out b1=&x d11=&x; run; ... View more

Hi - SAS/ETS software provides access to many different techniques for time series forecasting - for an overview check out: http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_intro_sect012.htm As a start you might want to check out PROC ESM which will create forecasts based on exponential smoothing models with optimized parameters. PROC ARIMA and PROC UCM for example can be used to create more advanced models later on, which also allow to incorporate independent variables for example. You will find plenty of examples in SAS/ETS software documentation - for example: http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_esm_sect024.htm Thanks, Udo ... View more

Hello - I might be wrong, but this question seems to be related to your post: http://communities.sas.com/message/114872#114872 Again I believe that PROC TIMESERIES (of SAS/ETS) could be of interest, since the start date variable in the OUTSUM data set tells you when the first nonmissing observation per BY group occurred. Then it should be easy to delete these observations from your original data set. Hope this makes sense. Regards, Udo ... View more

Hello - not sure if this helps, but this statement from documention might be useful: "For heteroscedastic regression models without GARCH effects, the errors are assumed to be uncorrelated — the heteroscedasticity models specified by the HETERO statement cannot be combined with an autoregressive model for the errors. Thus, when a HETERO statement is used, the NLAG= option cannot be specified unless the GARCH= option is also specified." (http://support.sas.com/documentation/cdl/en/etsug/63939/HTML/default/viewer.htm#etsug_autoreg_sect015.htm) Thanks, Udo ... View more