BookmarkSubscribeRSS Feed
Calcite | Level 5

## Calculation of rolling standard deviation using PROC EXPAND

Dear All,

I have the following dataset:

```data have;
input id date X;
datalines;
1  194503  .
1  194506  .
1  194509  0.12  194703  .
2  194706  0.2
2  194709  0.12  194712  0.32  194803  0.2;```

I would like to compute the rolling standard deviation (STDX)  for different IDs of the variable X using 3 observations (with no less than 2). For example, in row 5 after two observations of X (0.2 and 0.1), I want to compute STDX as 0.05. In row 6, after three observations of X (0.2, 0.1 and 0.3), I wnat to compute STDX as 0.082

```data want;
input id date X STDX;
datalines;
1  194503  .    .
1  194506  .    .
1  194509  0.1  .2  194703  .    .
2  194706  0.2  .
2  194709  0.1  0.052  194712  0.3  0.0822  194803  0.2  0.082;```

My attempt involves proc expand as follows. Yet, I'm not able to get the desired result. Moreover, I'm also getting the following warning: WARNING: The variable X has only 0 nonmissing observations, which is too few to apply
the conversion method. The result series is set to missing.

Any help would be highly appreciated. Many thanks!

```PROC EXPAND DATA=have OUT=want;
by id;
convert X=STDX / transformout=(MOVSTD 3 trim 2);
RUN;```
1 REPLY 1
Onyx | Level 15

## Re: Calculation of rolling standard deviation using PROC EXPAND

Proc Expand is an undoubtedly powerful utility tool, however, because it is not a programming tool, it lacks certain level of flexibility  that data step can conveniently offer.

``````data have;
input id date X;
datalines;
1  194503  .
1  194506  .
1  194509  0.1
2  194703  .
2  194706  0.2
2  194709  0.1
2  194712  0.3
2  194803  0.2
;

data want;
array _sd(0:2) _temporary_;
set have;
by id date;

if first.id then
do;
call missing(of _sd(*));
_n=0;
end;

_n+1;
_sd(mod(_n,3))=x;

if n(of _sd(*))>=2 then
stdx=std(of _sd(*));
drop _n;
run;``````
Discussion stats
• 1 reply
• 2154 views
• 0 likes
• 2 in conversation