No worries at all, you have been exceptionally helpful. I will test out Ksharps code and keep playing with this data! ... View more

This code is thorough and works swimmingly! Thank you thank you thank you, these were the kind of results I was expecting!! Just a quick question. As I also want to find the +/- 2 yr average SDEV for all stocks (regardless of 1262 and 1272 or not) how should I tweak the code? You will notice Ksharp provides a solution, but due to past concerns, is this an incorrect approach to take? I am just thinking surely this approach must be easier than that discussed above. Hmm, there must be a solution for this! Btw, if duplicate data arises this can be easily solved using: data test_vol_in; set test_vol_in; if permno=lag(permno) and date=lag(date) then delete; run; I am pleased progress is being made, you provide a very valuable contribution art! Almost there! -Katy ... View more

Hi art Thank you very much for your questions and helpfulness I was beginning to worry this problem may not get resolved. In response to your questions: DCLRDT is the date that the distribution code (1262 or 1272) is declared to the market. This being said, this date should act like the premise for the two years before and two years after calculation. I.e.: if the DCLRDT was 01-01-2010 then two years before should extend to 01-01-2008 for DATE and two years after should extend to 01-01-2012 for DATE, with respect to the averages for standard deviation. However the DCLRDT and the DATE for 1262 and 1272 should not be vastly different from one another. Therefore for simplicities sake I encourage using the DATE as the basis or starting point. If Permno 10239 has a number of 1262s and 1272s which overlap in the +/-2 year date ranges this is ok. The overlap of SDEV information for the same permno is fine. But I would like to treat each 1262 and 1272 separately. I have supplied no file called “have”. To be frank, I am only interested in 2 before and after analysis, not 1yr. I am not sure why this has been included. Does your code supplied satisfy the conditions discussed above? As there are concerns with the earlier posts. I would also like to ask you: how would your code change if we remove the 1262 and 1272 restriction? That is, for each PERMNO calculate the daily standard deviation (SDEV) for RET two years before (t= -1,-2) and two years after (t= +1,+2) surrounding each date recorded, or each observation. So the column called BEFORE, the average daily standard deviation across the years (–1,–2) is calculated for all observations. And the column called AFTER. This is an average daily standard deviation across the years (+1, +2) is also calculated for all observations. -I then subtract BEFORE-AFTER, to produce a difference column (DIFF) which should be produced for all the observations. If is is possible to see code for: (1) With 1262 and 1272 restriction (2) And without 1262 and 1272 restriction I would be unduly grateful. Thank you very much once again. ... View more

Thank you for your concern Richard, do you have any suggestions to improving the PERMNO and DATE order? Do you mean that these are not consecutive? This is not too much of a problem because the focus here is on average volatiles (standard deviations). My main objective is just to compare the typical size of the standard deviation two years before with two years after. Ksharp I am having difficulty with the 1st code produced actually. It appears that: "mean_two_year_after"  has the same value as "mean_two_year_before". This cannot be correct. I suspect there is a problem with the code, maybe at the standard devation calculations: proc sql; create table temp as select h.*,       case when  h.distcd in (1262 1272) then (select std(ret) from have where permno = h.permno and date between intnx('year',-1,h.date,'s') and h.date) else . end as std_one_year_before ,       case when  h.distcd in (1262 1272) then (select std(ret) from have where permno = h.permno and date between intnx('year',-2,h.date,'s') and h.date) else . end as std_two_year_before ,       case when  h.distcd in (1262 1272) then (select std(ret) from have where permno = h.permno and date between h.date and  intnx('year',1,h.date,'s')) else . end as std_one_year_after ,       case when  h.distcd in (1262 1272) then (select std(ret) from have where permno = h.permno and date between h.date and  intnx('year',2,h.date,'s')) else . end as std_two_year_after   from test_vol as h; I see that it contains: "  case when  h.distcd in (1262 1272)" this worries me. I shall provide an example of what I am looking to do, if permno 10008 had 1262 on 03/07/2010... I would like to calculate permos 10008 average standard deviation for 03/07/2008 to 03/07/2010, this is known as before (note the daily standard deviations used in the mean computation do not have to be associated  with their own 1262 and 1272 distcd). Then, following the same pattern, I would also like to calculate permnos 10008  average standard deviation from 03/07/2010 to 03/07/2012. If my data doesn't give me the full two years before, and after, that is OK just utilize the observations that I have. Are you sure the code above accurately does this? I have attempted to use your second code without the 1262 and 1272 but it appears that this is taking too long with my data. ... View more