BookmarkSubscribeRSS Feed
joebear64
Calcite | Level 5

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? 

2 REPLIES 2
PGStats
Opal | Level 21

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;
PG
PaigeMiller
Diamond | Level 26

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

--
Paige Miller

sas-innovate-wordmark-2025-midnight.png

Register Today!

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.


Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 550 views
  • 0 likes
  • 3 in conversation