topic calculating standard deviations within certain intervals in SAS Programming

calculating standard deviations within certain intervals

JKCho — Sat, 14 Nov 2020 22:53:48 GMT

Hello,

I know it is not hard but now facing a tricky case.

proc sql;
create table want as
 select *,(select std(x) from have where year between a.year-2 and a.year and code=a.code) as rolling_std
  from have as a;
quit;

I can use something as above but the thing is that I am now working with a date like 1988/05/05 instead of just year like shown in the example code.

I want to calculate standard deviations of observations over certain periods after certain events occur.

For example,

event date:1988/05/16

observations: MONTHLY stock returns

period: last 3 years before the event occurs

what I want: standard deviation of MONTHLY returns from 1985/05/15~1988/05/15, conceptually.

when it comes to doing this kind of job, I certainly recognize there would be the case that there is no observation on both or either starting date or ending date(like 1988/05/15 here in this example), so I also need to handle this issue as well.

What I think doing hard are

1. how to set month interval in the PROC SQL while I am using not "year" variable but "date" variable. I do not think I can just write date-365 or something. If so... well, I have thought too much

2. how to locate the closest alternative dates when there is no exact starting date or ending date.

Thank you for your help!

Re: calculating standard deviations within certain intervals

PaigeMiller — Sun, 15 Nov 2020 00:57:03 GMT

Show us the code with months that you have tried.

I do not think I can just write date-365 or something. If so... well, I have thought too much

Well, actually, that should work if you do it properly.

how to locate the closest alternative dates when there is no exact starting date or ending date.

I don't think this has been explained.

Re: calculating standard deviations within certain intervals

hhinohar — Mon, 16 Nov 2020 05:04:31 GMT

Hi JkCho,
It seems you want to filter by month while data resides in date and do standard deviation calc while it starts or ends in the middle of month accordingly.
If that is the case, you might want to use intck function to do interval calculation and group it and filter out month start or end cases using case clause in SQL.

intck function
https://documentation.sas.com/?cdcId=vdmmlcdc&cdcVersion=8.1&docsetId=casfedsql&docsetTarget=n096g3nwx05g7wn161ax2rdqmlfn.htm&locale=en

case expression
https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=casfedsql&docsetTarget=n0t7379re7ydjcn1rg93rlne17wt.htm&locale=en

There was a similar thread in the past.

https://communities.sas.com/t5/SAS-Procedures/rolling-standard-deviation-calculation/m-p/160026#M41708

Below is a sample code borrowed from thread above.

*sample data;
data have;
	format date date9. ;
		do code=1 to 4;
			do date="15May1980"d to "15May1999"d;
				ret=date;
				output;
			end;
		end;
run;

*create view starting from 1985 to 1988;
proc sql ;
create view _want as
select 
	 put(date,yymmn.) as ym,
	 date,
	 code,
	/*check interval and filter with case */
	 case when date < "15May1985"d
	   then .
	   when date > "15May1988"d then .
	   /*intck function option "C" specifies that continuous time is measured*/
	   else intck('month', "15May1985"d, date,'C')
	 end as interval,
	 ret
	  from have a
	  		/*filter 3 year interval starting from 15May1985 to 15May1988*/
	  		having interval between 0 and 36 and "15May1985"d <=date <= "15May1988"d
	  			order by code,ym,date;
quit;
*rolling standard deviation;
proc sql ;
create table want as select *, (select std(ret) from _want where interval between a.interval-36 and a.interval-1 and code=a.code) as rolling_std
from _want as a;
quit;

Re: calculating standard deviations within certain intervals

JKCho — Tue, 17 Nov 2020 09:26:23 GMT

oh. Yeah... Thank you so much. I was working on a similar code and realized that I have asked the question here!
Yes. your code works 🙂
Thank you again!

Re: calculating standard deviations within certain intervals

hhinohar — Tue, 17 Nov 2020 09:29:25 GMT

oh wow...

Never thought my reply would solve your problem.

Personally, I learned a lot about stock analysis and this was fun!

Have a good day!