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

how to compute rolling standard deviation ?

srikanthyadav44 — Wed, 21 Aug 2019 05:59:38 GMT

dear all

i have to compute rolling standard deviation based on past five years values of a variable.

my data set is as follows

Company_Name	year	sales	std. dev
20 Microns Ltd.	2005	565.2
20 Microns Ltd.	2006	668.9
20 Microns Ltd.	2007	869.1
20 Microns Ltd.	2008	1067.8
20 Microns Ltd.	2009	1374.9	=STDEV(C2:C6)
20 Microns Ltd.	2010	1768.2	=STDEV(C3:C7)
3I Infotech Ltd.	2005	2064.7
3I Infotech Ltd.	2006	2755.5
3I Infotech Ltd.	2007	3313.1
3I Infotech Ltd.	2008	4451
3I Infotech Ltd.	2009	5249.6	=STDEV(C8:C12)
3I Infotech Ltd.	2010	5195.3	=STDEV(C9:C13)

it is in panel data format with more than 3000 companies from 2005 to 2018.

the calculation of standard deviation should be done company-wise.

i want to give equal weightage to all the observation for a company.

there are no missing values in the dataset.

thanks in advance

Re: how to compute rolling standard deviation ?

PeterClemmensen — Wed, 21 Aug 2019 06:10:47 GMT

Do you have a SAS/ETS license?

Re: how to compute rolling standard deviation ?

PeterClemmensen — Wed, 21 Aug 2019 06:14:48 GMT

I like to use PROC EXPAND for problems like this. However, if you do not have an ETS license, you can use PROC SQL and do something like this

data have;
input Company_Name :$50. year sales;
infile datalines dlm=',';
datalines;
20 Microns Ltd.,2005,565.2
20 Microns Ltd.,2006,668.9
20 Microns Ltd.,2007,869.1
20 Microns Ltd.,2008,1067.8
20 Microns Ltd.,2009,1374.9
20 Microns Ltd.,2010,1768.2
3I Infotech Ltd.,2005,2064.7
3I Infotech Ltd.,2006,2755.5
3I Infotech Ltd.,2007,3313.1
3I Infotech Ltd.,2008,4451
3I Infotech Ltd.,2009,5249.6
3I Infotech Ltd.,2010,5195.3
;

proc sql;
   create table want as
   select *, 
          (select std(sales) from have 
             where Company_Name=a.Company_Name
               and a.year-5 le year le a.year)
                 as stddev format=8.2
   from have as a;
quit;