how to compute rolling standard deviation ?

[srikanthyadav44]

deal all, 

i have to compute rolling standard deviation of Variable X of each company  for the preceding 12 quarters. i have the panel data of nearly 5000 companies and 37 quarters.  i have the quarterly data in the following format. 

Company_Name	Quarter	X
20 Microns Ltd.	Mar-10	0.29
20 Microns Ltd.	Jun-10	0.29
20 Microns Ltd.	Sep-10	0.29
20 Microns Ltd.	Dec-10	0.42
20 Microns Ltd.	Mar-11	0.29
20 Microns Ltd.	Jun-11	0.29
20 Microns Ltd.	Sep-11	0.29
20 Microns Ltd.	Dec-11	0
20 Microns Ltd.	Mar-12	0
20 Microns Ltd.	Jun-12	0.77
20 Microns Ltd.	Sep-12	0.73
20 Microns Ltd.	Dec-12	0
20 Microns Ltd.	Mar-13	3.58
20 Microns Ltd.	Jun-13	1.84
20 Microns Ltd.	Sep-13	4.88
20 Microns Ltd.	Dec-13	0
20 Microns Ltd.	Mar-14	0
20 Microns Ltd.	Jun-14	0
20 Microns Ltd.	Sep-14	0
20 Microns Ltd.	Dec-14	0
20 Microns Ltd.	Mar-15	0.03
20 Microns Ltd.	Jun-15	0
20 Microns Ltd.	Sep-15	0
20 Microns Ltd.	Dec-15	0
20 Microns Ltd.	Mar-16	0
20 Microns Ltd.	Jun-16	0
20 Microns Ltd.	Sep-16	0
20 Microns Ltd.	Dec-16	0.11
20 Microns Ltd.	Mar-17	0.03
20 Microns Ltd.	Jun-17	1.32
20 Microns Ltd.	Sep-17	0.08
20 Microns Ltd.	Dec-17	0.1
20 Microns Ltd.	Mar-18	0.37
20 Microns Ltd.	Jun-18	0.09
20 Microns Ltd.	Sep-18	0.33
20 Microns Ltd.	Dec-18	0.26
20 Microns Ltd.	Mar-19	0.11
3I Infotech Ltd.	Mar-10	39.02
3I Infotech Ltd.	Jun-10	41.18
3I Infotech Ltd.	Sep-10	37.33
3I Infotech Ltd.	Dec-10	40.36
3I Infotech Ltd.	Mar-11	39.46
3I Infotech Ltd.	Jun-11	38.8
3I Infotech Ltd.	Sep-11	36.43
3I Infotech Ltd.	Dec-11	22.01
3I Infotech Ltd.	Mar-12	18.04
3I Infotech Ltd.	Jun-12	57.82
3I Infotech Ltd.	Sep-12	54.74
3I Infotech Ltd.	Dec-12	55.02
3I Infotech Ltd.	Mar-13	55.35

i want the output in the following format

Company_Name	Quarter	X	Rolling_mean	Rolling_Std.dev
20 Microns Ltd.	Mar-10	0.29
20 Microns Ltd.	Jun-10	0.29
20 Microns Ltd.	Sep-10	0.29
20 Microns Ltd.	Dec-10	0.42
20 Microns Ltd.	Mar-11	0.29
20 Microns Ltd.	Jun-11	0.29
20 Microns Ltd.	Sep-11	0.29
20 Microns Ltd.	Dec-11	0
20 Microns Ltd.	Mar-12	0
20 Microns Ltd.	Jun-12	0.77
20 Microns Ltd.	Sep-12	0.73
20 Microns Ltd.	Dec-12	0
20 Microns Ltd.	Mar-13	3.58	0.56	0.94
20 Microns Ltd.	Jun-13	1.84	0.68	1.00
20 Microns Ltd.	Sep-13	4.88	1.03	1.52
20 Microns Ltd.	Dec-13	0	1.01	1.54
20 Microns Ltd.	Mar-14	0	0.97	1.56
20 Microns Ltd.	Jun-14	0	0.95	1.57
20 Microns Ltd.	Sep-14	0	0.93	1.58
20 Microns Ltd.	Dec-14	0	0.91	1.59
20 Microns Ltd.	Mar-15	0.03	0.91	1.59
20 Microns Ltd.	Jun-15	0	0.91	1.59
20 Microns Ltd.	Sep-15	0	0.85	1.61
20 Microns Ltd.	Dec-15	0	0.79	1.63

please suggest a suitable SAS code.

thanks in advance  

[PaigeMiller]

PROC EXPAND

Example: https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=etsug&docsetTarget=etsug_... using the MOVSTD option instead of the MOVAVG option.

[Reeza]

Can you assume you have data for every quarter consecutively and are not missing months?
I'll leave the part of adding logic to only calculate once you have 12 quarters up to you - NMISS() function + IF.

data want;
array p{0:11} _temporary_;
set have;
by companyName Quarter;
if first.companyName then call missing(of p{*});
p{mod(_n_,12)} = price;
rolling_mean= mean(of p{*});
rolling_std= std(of p{*});
run;


https://gist.github.com/statgeek/27e23c015eae7953eff2

[novinosrin · Accepted solution]

data have;
 input Company_Name  $16.	Quarter $	X;
 cards;
20 Microns Ltd.	Mar-10	0.29
20 Microns Ltd.	Jun-10	0.29
20 Microns Ltd.	Sep-10	0.29
20 Microns Ltd.	Dec-10	0.42
20 Microns Ltd.	Mar-11	0.29
20 Microns Ltd.	Jun-11	0.29
20 Microns Ltd.	Sep-11	0.29
20 Microns Ltd.	Dec-11	0
20 Microns Ltd.	Mar-12	0
20 Microns Ltd.	Jun-12	0.77
20 Microns Ltd.	Sep-12	0.73
20 Microns Ltd.	Dec-12	0
20 Microns Ltd.	Mar-13	3.58
20 Microns Ltd.	Jun-13	1.84
20 Microns Ltd.	Sep-13	4.88
20 Microns Ltd.	Dec-13	0
20 Microns Ltd.	Mar-14	0
20 Microns Ltd.	Jun-14	0
20 Microns Ltd.	Sep-14	0
20 Microns Ltd.	Dec-14	0
20 Microns Ltd.	Mar-15	0.03
20 Microns Ltd.	Jun-15	0
20 Microns Ltd.	Sep-15	0
20 Microns Ltd.	Dec-15	0
20 Microns Ltd.	Mar-16	0
20 Microns Ltd.	Jun-16	0
20 Microns Ltd.	Sep-16	0
20 Microns Ltd.	Dec-16	0.11
20 Microns Ltd.	Mar-17	0.03
20 Microns Ltd.	Jun-17	1.32
20 Microns Ltd.	Sep-17	0.08
20 Microns Ltd.	Dec-17	0.1
20 Microns Ltd.	Mar-18	0.37
20 Microns Ltd.	Jun-18	0.09
20 Microns Ltd.	Sep-18	0.33
20 Microns Ltd.	Dec-18	0.26
20 Microns Ltd.	Mar-19	0.11
3I Infotech Ltd.	Mar-10	39.02
3I Infotech Ltd.	Jun-10	41.18
3I Infotech Ltd.	Sep-10	37.33
3I Infotech Ltd.	Dec-10	40.36
3I Infotech Ltd.	Mar-11	39.46
3I Infotech Ltd.	Jun-11	38.8
3I Infotech Ltd.	Sep-11	36.43
3I Infotech Ltd.	Dec-11	22.01
3I Infotech Ltd.	Mar-12	18.04
3I Infotech Ltd.	Jun-12	57.82
3I Infotech Ltd.	Sep-12	54.74
3I Infotech Ltd.	Dec-12	55.02
3I Infotech Ltd.	Mar-13	55.35
;


data want;
 array t(0:12) _temporary_;
 do _n_=0 by 1 until(last.Company_Name or _n_=12);
   set have;
   by Company_Name;
   if first.company_name then call missing(of t(*));
   t(_n_)=x;
   if nmiss(of t(*))=0 then do;
    r_mean=round(mean(of t(*)),.01);
    r_std_dev=round(std(of t(*)),.01);
   end;
   output;
 end;
run;