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.
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
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.
Ready to level-up your skills? Choose your own adventure.