BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Kayomole
Calcite | Level 5

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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

7 REPLIES 7
ballardw
Super User

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?

Kayomole
Calcite | Level 5

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.

ballardw
Super User

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;

 

 

Kayomole
Calcite | Level 5
I will try this. Thank you
Ksharp
Super User
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;


Kayomole
Calcite | Level 5

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.

Ksharp
Super User

Hash Table might be a good choice for BIG table.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

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