topic Re: how to compute rolling standard deviation ? in SAS Programming

how to compute rolling standard deviation ?

srikanthyadav44 — Fri, 27 Nov 2020 16:58:15 GMT

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

Re: how to compute rolling standard deviation ?

PaigeMiller — Fri, 27 Nov 2020 17:12:38 GMT

PROC EXPAND

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

Re: how to compute rolling standard deviation ?

Reeza — Fri, 27 Nov 2020 17:22:28 GMT

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

Re: how to compute rolling standard deviation ?

novinosrin — Fri, 27 Nov 2020 17:25:00 GMT


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;