topic Re: Rolling standard deviation by category in SAS Programming

Rolling standard deviation by category

joebear64 — Fri, 28 Jun 2019 19:05:15 GMT

I have the following table:

Product | Category | Date |X

A |1 |05/30/2019 | 10

A |1 |05/31/2019 | 9

A |1 |06/03/2019 | 11

A |1 |06/04/2019 | 10

A |1 |06/05/2019 | 8

A |1 |06/06/2019 | 3

A |1 |06/07/2019 | 12

A |2 |05/30/2019 | 2

A |2 |05/31/2019 | 4

A |2 |06/03/2019 | 1

A |2 |06/04/2019 | 3

A |2 |06/05/2019 | 7

A |2 |06/06/2019 | 2

A |2 |06/07/2019 | 1

B |1 |05/30/2019 |55

A |1 |05/30/2019 | 66

And need to add a rolling 5 day standard deviation for each product/category. For example, my output needs to be:

Product | Category | Date |X | Standard deviation

A |1 |05/30/2019 | 10 | .

A |1 |05/31/2019 | 9 | .

A |1 |06/03/2019 | 11 | .

A |1 |06/04/2019 | 10 | .

A |1 |06/05/2019 | 8 | 1.140175

A |1 |06/06/2019 | 3 | 3.114482

A |1 |06/07/2019 | 12 | 3.563706

A |2 |05/30/2019 | 2 | .

A |2 |05/31/2019 | 4 | .

A |2 |06/03/2019 | 1 | .

A |2 |06/04/2019 | 3 | .

A |2 |06/05/2019 | 7 | 2.3021

A |2 |06/06/2019 | 2 | 2.3021

A |2 |06/07/2019 | 1 | 2.4899

B |1 |05/30/2019 |55 | .

A |1 |05/30/2019 | 66 | .

Im able to get the output if I first create a table with just one category/product and then do:

proc sort data=RAW;
by product category date;
run;

data test1;
set RAW;
obs + 1;
by product category;
if first.product or first.category then obs = 1;
run;

data calcs;
set test1;

array a[5] arr1-arr5;

retain arr1-arr5 .;

do tmp=5 to 2 by -1;
a[tmp] = a[tmp-1];
end;
a[1] = X;

if _n_ ge 5 then do;
std = std(of arr1-arr5);
end;

run;

Is there anyway I can edit the array above so it restarts when the product/category changes?

Re: Rolling standard deviation by category

PGStats — Sat, 29 Jun 2019 04:26:43 GMT

It is quite simple with a DO UNTIL loop and the MOD function:

data have;
infile datalines dlm='|';
input Product $ Category Date :mmddyy. X;
format date yymmdd10.;
datalines;
A           |1               |05/30/2019 | 10
A           |1               |05/31/2019 | 9 
A           |1               |06/03/2019 | 11
A           |1               |06/04/2019 | 10
A           |1               |06/05/2019 | 8
A           |1               |06/06/2019 | 3
A           |1               |06/07/2019 | 12
A           |2               |05/30/2019 | 2
A           |2               |05/31/2019 | 4
A           |2               |06/03/2019 | 1    
A           |2               |06/04/2019 | 3
A           |2               |06/05/2019 | 7
A           |2               |06/06/2019 | 2     
A           |2               |06/07/2019 | 1
B           |1               |05/30/2019 |55
B           |1               |05/30/2019 | 66 
;

proc sort data=have; by product category date; run;

data want;
array xx{5};
do i = 1 by 1 until(last.category);
    set have; by product category;
    xx{1+mod(i,dim(xx))} = x;
    if i >= 5 then std = std(of xx{*});
    output;
    end;
drop xx: i;
run;

Re: Rolling standard deviation by category

PaigeMiller — Sat, 29 Jun 2019 10:54:44 GMT

If you have PROC EXPAND, that will do the calculations, so you don't have to write and debug your own code.