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?
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;
If you have PROC EXPAND, that will do the calculations, so you don't have to write and debug your own code.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.