Hi, if I have data that looks like below , and I want to find the first row (by date) that has a criteria in another field and then apply a formula.  So in the below example where the first Flag=Y starts, use the previous 3 Prices MEAN and the NEW_DIFF calculate as normal by itself  .. and where the FLAG NE Y use the noraml Price and the Diff   .. As you can see I'm trying to smooth out the 3 outliers identified in the Flag=Y. here's what i mean HAVE: Date        Flag        Price               Diff Dec'99           N                $21                   . Jan'00        N             $23             9% Feb'00        N             $28            21% Mar'00        N             $26           -7% Apr'00        Y             $256               884% May'00       Y             $274                7% Jun'00        Y             $300                 9% Jul'00         N              $29            -90% Aug'00       N              $48             66% WANT1: Date        Flag        Price             Diff                    New_Price            New_Diff Dec'99           N                $21                .                               $21                          . Jan'00        N             $23             9%                     $23                   9% Feb'00        N             $28            21%                    $28                   21% Mar'00        N             $26            -7%                     $26                  -7% Apr'00        Y             $256           884%                   $25.6                 -1.5% May'00       Y             $274             7%                    $26.5                  3.5% Jun'00        Y             $300            9%                     $26                    -1.8% Jul'00         N              $29            -90%                   $29                    -90% Aug'00       N              $48             66%                    $48                     66% Udo had a great recommendation to use proc esm, and that works perfectly if there is only 1 outlier, but I have 3. And I thought using the mean of the previous 3 inputs can fix the outliers. ... View more

Hi,  I have many series of data (timed-series), so I'm looking for a automated procedure for this fix.  But my challenge is that I'm following a price (6-month rolling average) over a period of time that has different start dates for different series (eg. stores and products)...  and the main challenge is that I have these spikes in prices, that could happen at any point in time and for an undisclosed time-frame.  See Sample charts below.  I need to somehow smooth out these spikes, but only where they are real explosive.  So does anyone know in SAS how I can smooth out the data if for example the data has a 200% growth over a 1 year period.. but since it's gradually increasing since this is monthly data, I have to be careful I don't smooth out good data.  What I mean by that is that if the first month it increase by 20% (reasonable) and second month again another 20% (reasonable), and so on,, in one year it is 240%,.. how can I capture and smooth that.   Or I'd love to hear any other suggestions on how to deal wiht this issue.  I do have SAS/ETS. Thanks so much. For example I don't really want to smooth out anything in SAMPLE -A but do want the spike in SAMPLE -B ... View more

Hi,  I have a large task... This may be a 2 step process. I have 2 large datasets with about 3 by variables and Price.  They're both time-series data, where one (Data1) goes as far back as 15 years and the second (Data2) only 5 years back.  One of the problem is that the By varaibles (eg, Stores) some start further eg.6 years and some 4 years in Data2... Step1 - I would like to delete the first 6 months of Data2 (because of rolling averages) and the Step 2- STITCH where the period of the Data2 ended with the furthest data from Data1. Sample Data1: Store      Date              Price              Product A            2000.01           234                A1 A            2000.02           246                A1 A            2000.03           258                A1 B            2000.01           564                B2 B            2000.02           534                B2 B            2000.03           533                B2 B            2000.01           345                B1 B            2000.02           354                B1 Sample Data2: similar to Data1 but later start dates by Product and Store.. to stitch Data2 after I delete the first 6 months (in the sample 1 month).. Is this possible ? thanks ... View more

I'm using this code below, but I'd like to add 2 more by-groups.. ?  Thanks data by patient; retain num_sum 0 if first.patient then do;     count=0;   num sum=0; end; copunt+1; last&n=lag&n(num); if count gt &n then num_sum=sum(num_sum,num,-last&n); else num_sum=sum(num_sum,num); if count ge &n then mov_aver=num_sum/&n; else mov_aver=.; run; ... View more