Solved
Contributor
Posts: 25

# standard deviation

Hi all, I need help on calculating the moving standard deviaiton. e.g. for 19950201, I want to find the values of the trailing 30 days SD of returns and then the SD of trailing days of 19950202 etc. Thank you all.

 date NCUSIP TICKER RET 19950103 2110 ANAFF -0.125 19950104 2110 ANAFF 0.285714 19950105 2110 ANAFF -0.22222 19950106 2110 ANAFF 0.142857 19950109 2110 ANAFF 0 19950110 2110 ANAFF 0.125 19950111 2110 ANAFF 0 19950112 2110 ANAFF -0.22222 19950113 2110 ANAFF 0.142857 19950116 2110 ANAFF -0.125 19950117 2110 ANAFF -0.07143 19950118 2110 ANAFF 0.076923 19950119 2110 ANAFF -0.07143 19950120 2110 ANAFF 0.153846 19950123 2110 ANAFF -0.2 19950124 2110 ANAFF 0.25 19950125 2110 ANAFF 0.2 19950126 2110 ANAFF -0.33333 19950127 2110 ANAFF 0 19950130 2110 ANAFF 0.25 19950131 2110 ANAFF 0 19950201 2110 ANAFF 0 19950202 2110 ANAFF 0.1 19950203 2110 ANAFF -0.27273 19950206 2110 ANAFF 0.333333 19950207 2110 ANAFF -0.0625 19950208 2110 ANAFF 0.133333 19950209 2110 ANAFF -0.29412 19950210 2110 ANAFF 0.416667 19950213 2110 ANAFF -0.05882 19950214 2110 ANAFF -0.25 19950215 2110 ANAFF 0.333333 19950216 2110 ANAFF -0.1875 19950217 2110 ANAFF 0.153846 19950221 2110 ANAFF 0 19950222 2110 ANAFF -0.13333 19950223 2110 ANAFF 0 19950224 2110 ANAFF 0.115385 19950227 2110 ANAFF -0.10345 19950228 2110 ANAFF 0.115385

Accepted Solutions
Solution
‎12-01-2016 07:03 AM
Super User
Posts: 12,316

## Re: standard deviation

How do you want to account for differing numbers of days with ret values, if at all? Is the important thing 30 calendar days or 30 results days? if the the first then you should first make sure that there are all of the dates, even if the ret variable is missing.

The basic idea if I understand would be

STD30 = Std (lag1(ret),lag2(ret),lag3(ret), ...,lag30(ret));

However if you do want the values within each ticker then something like:

data want;

set have;

by ticker ;

if first.ticker then counter=0;

counter + 1;

STD30 = Std (lag1(ret),lag2(ret),lag3(ret), ...,lag30(ret));

if counter < 30 then call missing(STD30);

run;

All Replies
Super User
Posts: 12,316

## Re: standard deviation

I think you need to define "trailing days" to us. I am not sure I understand which direction in time you are referencing.

I also am assuming that you mean for this to be within a specific (or each) NCUSIP / Ticker.

Contributor
Posts: 25

## Re: standard deviation

Unfortunately, I don't have access to SAS/ETS. I was wondering if there is a way I could do it with proc sql;

By trailing days, I mean days before. For each day, I want to find the standard deviation of returns in the preceding 30 days.

Solution
‎12-01-2016 07:03 AM
Super User
Posts: 12,316

## Re: standard deviation

How do you want to account for differing numbers of days with ret values, if at all? Is the important thing 30 calendar days or 30 results days? if the the first then you should first make sure that there are all of the dates, even if the ret variable is missing.

The basic idea if I understand would be

STD30 = Std (lag1(ret),lag2(ret),lag3(ret), ...,lag30(ret));

However if you do want the values within each ticker then something like:

data want;

set have;

by ticker ;

if first.ticker then counter=0;

counter + 1;

STD30 = Std (lag1(ret),lag2(ret),lag3(ret), ...,lag30(ret));

if counter < 30 then call missing(STD30);

run;

Contributor
Posts: 25

## Re: standard deviation

I will try this. Thank you
Super User
Posts: 10,389

## Re: standard deviation

```If your table is not big, SQL is good for this.
CODE NOT TESTED

select *,( select std(ret) from have where ncusip=a.ncusip and date between a.date and a.date+30 ) as std
from have as a;
quit;

```
Contributor
Posts: 25

## Re: standard deviation

I actually used the code you suggested. With smaller tables, it works fine. However, the table is big and it has been running for over three days. Maybe proc sql is not good for the big table afterall. Thanks anyway.

Super User
Posts: 10,389

## Re: standard deviation

Hash Table might be a good choice for BIG table.

☑ This topic is solved.