calculating standard deviations within certain intervals

[JKCho]

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!

[PaigeMiller]

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.

[hhinohar · Accepted solution]

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=n096g3n...

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

There was a similar thread in the past.

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

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;

[JKCho]

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!

[hhinohar]

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!