SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

standard deviation

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

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.

 

 

dateNCUSIPTICKERRET
199501032110ANAFF-0.125
199501042110ANAFF0.285714
199501052110ANAFF-0.22222
199501062110ANAFF0.142857
199501092110ANAFF0
199501102110ANAFF0.125
199501112110ANAFF0
199501122110ANAFF-0.22222
199501132110ANAFF0.142857
199501162110ANAFF-0.125
199501172110ANAFF-0.07143
199501182110ANAFF0.076923
199501192110ANAFF-0.07143
199501202110ANAFF0.153846
199501232110ANAFF-0.2
199501242110ANAFF0.25
199501252110ANAFF0.2
199501262110ANAFF-0.33333
199501272110ANAFF0
199501302110ANAFF0.25
199501312110ANAFF0
199502012110ANAFF0
199502022110ANAFF0.1
199502032110ANAFF-0.27273
199502062110ANAFF0.333333
199502072110ANAFF-0.0625
199502082110ANAFF0.133333
199502092110ANAFF-0.29412
199502102110ANAFF0.416667
199502132110ANAFF-0.05882
199502142110ANAFF-0.25
199502152110ANAFF0.333333
199502162110ANAFF-0.1875
199502172110ANAFF0.153846
199502212110ANAFF0
199502222110ANAFF-0.13333
199502232110ANAFF0
199502242110ANAFF0.115385
199502272110ANAFF-0.10345
199502282110ANAFF0.115385

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

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;

 

 

View solution in original post


All Replies
Super User
Posts: 10,483

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.

 

Do you have access to the SAS/ETS procedures?

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: 10,483

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: 9,671

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: 9,671

Re: standard deviation

Hash Table might be a good choice for BIG table.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 256 views
  • 0 likes
  • 3 in conversation