topic Re: standard deviation in SAS Data Management

standard deviation

Kayomole — Mon, 21 Nov 2016 12:54:26 GMT

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

Re: standard deviation

ballardw — Mon, 21 Nov 2016 15:50:41 GMT

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?

Re: standard deviation

Kayomole — Mon, 21 Nov 2016 16:07:18 GMT

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.

Re: standard deviation

ballardw — Mon, 21 Nov 2016 20:44:46 GMT

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;

Re: standard deviation

Ksharp — Tue, 22 Nov 2016 03:27:28 GMT

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;

Re: standard deviation

Kayomole — Tue, 22 Nov 2016 08:05:25 GMT

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.

Re: standard deviation

Kayomole — Tue, 22 Nov 2016 08:08:01 GMT

I will try this. Thank you

Re: standard deviation

Ksharp — Tue, 22 Nov 2016 09:54:42 GMT

Hash Table might be a good choice for BIG table.