subquery - SAS: standard deviation on unfixed rolling window -

i think posted similar question before. time struggling data id.

my data looks like

 date        stock    value    standard_deviation 01/01/2015    vod      18       ... 01/01/2015    vod      15       ... 01/01/2015    vod       5       ... 03/01/2015    vod      66       ... 03/01/2015    vod       7       ... 04/01/2015    vod      19       ... 04/01/2015    vod       7       ... 05/01/2015    vod      3        ... 06/01/2015    vod      7        ...  .....          ...     ...     ... 01/01/2015    rbs      58       ... 01/01/2015    rbs      445      ... 01/01/2015    rbs      44       ... 03/01/2015    rbs      57       ... 

i need work out moving average/std deviation for each stock based on (-3,+3) trading days.

since trading days (not calendar days), , there different number of trades in each day, created sub-query , applied following code.

data want; set input;   date; retain gdate; if first.date gdate+1; run;  proc sort data=want; stock gdate ; run;   proc sql; create table want1 select    h.stock, h.date, h.value, ( select std(s.value) want s h.gdate between s.gdate-2 , s.gdate+2) std  want h   group   stock;   quit; 

i tried group stock. however, code ignored stock group , gave me moving std of whole period. need moving std for different stocks.

anyone can give me idea ? !

let's familiarized proc expand! it's going new best friend in time series.

proc expand allows basically of common transformations (and ones didn't know existed) on data.

first, answer question:

step 1: combine values 1 trading day per stock

proc sql noprint;     create table have2         select date, stock, sum(value) total_value         have         group stock, date         order stock, date; quit; 

step 2: use proc expand compute +/- 3 day centered moving standard deviation

proc expand data=have2              out=want;     id date;     stock;      convert total_value = standard_deviation / transform=(cmovstd 7); run; 

step 3: merge original table

proc sort data=have;     stock date; run;  data want2;     merge have           want;     stock date; run; 

explanation

we exploiting use of by-group processing , existing procedure bulk of work us. sas likes doesn't forward due how language designed, , proc expand 1 of few procedures able forward in data without lot of work. bonus of procedure doesn't break if there gaps in time series, can perform operations on kind of sequential data.

one of transformation operations, cmovstd, apply centered moving standard deviation on data in order achieve gathering future data moving standard deviation. note chose window of 7 +/- 3 day centered moving standard deviation. because need:

3 past days       | +3 current day       | +1 3 future days     | +3                   | 7 = window size 

or, total of 7 days in our window. if wanted +/- 2 day centered moving standard deviation, window 5:

2 past days       | +2 current day       | +1 2 future days     | +2                   | 5 = window size 

if choose number, have 1 or more lagged days make window choice valid. example, window of 4 yield:

2 past days       | +2 current day       | +1 1 future day      | +1                   | 4 = window size 

proc expand swiss army knife time series. interpolate, extrapolate, transform, , convert between time periods in 1 step. may find useful in following situations:

1. applying moving (average, std, etc.)

proc expand data=have                   out=want;     <by variable(s)>;     id <date variable>;     convert <numeric variable(s)> = <new variable name> / transform=(<operation> <window>);  run; 

2. filling in time gaps

proc expand data=have              out=want             to=<day, month, year, etc.>;                      <by variable(s)>;     id date;     convert <numeric variable(s)> </method=<interpolation method> >; run;