Calculate Cumulative Returns around certain announcement date

[Agent1592]

Dear SAS users:

I am trying to calculate cumulative returns 5 business days before and after Middate. I tried a code but unfortunately is not working for my case. Something is wrong with counting the days. Could you please help and let me know an easier way to compute returns around a date.

*First descending order to get the trading days before Middate;
proc sort data=test out=temp1;
   where date lt Middate;
   by permno Middate descending date; *descending order is intentional;
run;

data temp1; set temp1;
   by id Middate;
   if first.Middate then td_count=0;
   td_count=td_count-1; *increments in negative direction;
   retain td_count;
run;

proc sort data=test out=temp2;
   where date ge Middate;
   by id Middate date; *ascending order as default;
run;
data temp2; set temp2 ;
   by permno Middate;
   if first.Middate and date=Middate then td_count=0;
   td_count=td_count+1; *increments in positive direction;
   if date=Middate then td_count=0;
   retain td_count;
run;

data test1; set temp1 temp2;format date Date9.; run;

/*a) Computing Compounded returns around */ 
data ret;
  set test1;
  _ret_plus1=ret+1;
run;
proc sort data=ret; by  id Middate; run;

proc univariate data=ret noprint;
  where -10<=td_count<=10 and intck('day',Middate,date)<=10;
  by id Middate;
  var _ret_plus1;
  output out=Ret1 n=n1 geomean=gm_ret_plus1;
run;

data Ret1;
  set Ret1;
  RET1=gm_ret_plus1**n1-1;
run; 

data WORK.TEST;
  infile datalines dsd truncover;
  input ID:8. DATE:DATE9. RET:11.6 Middate:DATE9.;
  format ID 8. DATE DATE9. RET 11.6 Middate DATE9.;
  label ID="ID" DATE="Date of Observation" RET="Returns";
datalines4;
10002,15JUN2004,-0.022688,14SEP2004
10002,16JUN2004,0.008333,14SEP2004
10002,17JUN2004,-0.005313,14SEP2004
10002,18JUN2004,-0.000593,14SEP2004
10002,21JUN2004,0.005344,14SEP2004
10002,22JUN2004,0.033077,14SEP2004
10002,23JUN2004,0.013150,14SEP2004
10002,24JUN2004,-0.024831,14SEP2004
10002,25JUN2004,-0.007523,14SEP2004
10002,28JUN2004,0.022741,14SEP2004
10002,29JUN2004,-0.006842,14SEP2004
10002,30JUN2004,0.005741,14SEP2004
10002,01JUL2004,-0.021690,14SEP2004
10002,02JUL2004,0.022170,14SEP2004
10002,06JUL2004,-0.021119,14SEP2004
10002,07JUL2004,-0.005831,14SEP2004
10002,08JUL2004,0.000587,14SEP2004
10002,09JUL2004,-0.025205,14SEP2004
10002,12JUL2004,0.036681,14SEP2004
10002,13JUL2004,0.017401,14SEP2004
10002,14JUL2004,0.003421,14SEP2004
10002,15JUL2004,-0.017046,14SEP2004
10002,16JUL2004,-0.020231,14SEP2004
10002,19JUL2004,0.006490,14SEP2004
10002,20JUL2004,0.011723,14SEP2004
10002,21JUL2004,-0.003476,14SEP2004
10002,22JUL2004,0.002326,14SEP2004
10002,23JUL2004,-0.006381,14SEP2004
10002,26JUL2004,-0.004086,14SEP2004
10002,27JUL2004,0.029308,14SEP2004
10002,28JUL2004,-0.022779,14SEP2004
10002,29JUL2004,0.024476,14SEP2004
10002,30JUL2004,0.000000,14SEP2004
10002,02AUG2004,-0.027873,14SEP2004
10002,03AUG2004,-0.015799,14SEP2004
10002,04AUG2004,0.007729,14SEP2004
10002,05AUG2004,-0.023009,14SEP2004
10002,06AUG2004,-0.003623,14SEP2004
10002,09AUG2004,-0.009091,14SEP2004
10002,10AUG2004,0.064832,14SEP2004
10002,11AUG2004,-0.024124,14SEP2004
10002,12AUG2004,-0.025309,14SEP2004
10002,13AUG2004,0.010266,14SEP2004
10002,16AUG2004,0.017932,14SEP2004
10002,17AUG2004,-0.017616,14SEP2004
10002,18AUG2004,0.015541,14SEP2004
10002,19AUG2004,0.006474,14SEP2004
10002,20AUG2004,0.013450,14SEP2004
10002,23AUG2004,-0.019042,14SEP2004
10002,24AUG2004,0.014706,14SEP2004
10002,25AUG2004,0.023188,14SEP2004
10002,26AUG2004,0.005666,14SEP2004
10002,27AUG2004,0.012958,14SEP2004
10002,30AUG2004,0.000000,14SEP2004
10002,31AUG2004,0.000000,14SEP2004
10002,01SEP2004,-0.021691,14SEP2004
10002,02SEP2004,0.056282,14SEP2004
10002,03SEP2004,-0.056512,14SEP2004
10002,07SEP2004,0.039931,14SEP2004
10002,08SEP2004,-0.010971,14SEP2004
10002,09SEP2004,0.012202,14SEP2004
10002,10SEP2004,0.018082,14SEP2004
10002,13SEP2004,0.002691,14SEP2004
10002,14SEP2004,0.000000,14SEP2004
10002,15SEP2004,0.010811,14SEP2004
10002,16SEP2004,0.024064,14SEP2004
10002,17SEP2004,-0.007311,14SEP2004
10002,20SEP2004,-0.023146,14SEP2004
10002,21SEP2004,0.023694,14SEP2004
10002,22SEP2004,-0.018411,14SEP2004
10002,23SEP2004,-0.021972,14SEP2004
10002,24SEP2004,0.014247,14SEP2004
10002,27SEP2004,-0.046461,14SEP2004
10002,28SEP2004,0.016997,14SEP2004
10002,29SEP2004,0.010585,14SEP2004
10002,30SEP2004,0.036384,14SEP2004
10002,01OCT2004,0.010106,14SEP2004
10002,04OCT2004,0.001053,14SEP2004
10002,05OCT2004,-0.000526,14SEP2004
10002,06OCT2004,-0.000526,14SEP2004
10002,07OCT2004,-0.000527,14SEP2004
10002,08OCT2004,-0.004215,14SEP2004
10002,11OCT2004,0.000000,14SEP2004
10002,12OCT2004,-0.038624,14SEP2004
10002,13OCT2004,0.008255,14SEP2004
10002,14OCT2004,-0.006550,14SEP2004
10002,15OCT2004,0.018132,14SEP2004
10002,18OCT2004,-0.009174,14SEP2004
10002,19OCT2004,-0.019608,14SEP2004
10002,20OCT2004,0.022222,14SEP2004
10002,21OCT2004,-0.021739,14SEP2004
10002,22OCT2004,0.015000,14SEP2004
10002,25OCT2004,0.007115,14SEP2004
10002,26OCT2004,0.005435,14SEP2004
10002,27OCT2004,0.026486,14SEP2004
10002,28OCT2004,0.000527,14SEP2004
10002,29OCT2004,0.000000,14SEP2004
10002,01NOV2004,-0.004211,14SEP2004
10002,02NOV2004,0.002114,14SEP2004
10002,03NOV2004,0.002110,14SEP2004
10002,04NOV2004,0.052105,14SEP2004
10002,05NOV2004,-0.017009,14SEP2004
10002,08NOV2004,-0.015776,14SEP2004
10002,09NOV2004,-0.057394,14SEP2004
10002,10NOV2004,0.033461,14SEP2004
10002,11NOV2004,0.061040,14SEP2004
10002,12NOV2004,0.009505,14SEP2004
10002,15NOV2004,0.037661,14SEP2004
10002,16NOV2004,-0.032951,14SEP2004
10002,17NOV2004,0.009383,14SEP2004
10002,18NOV2004,-0.001957,14SEP2004
10002,19NOV2004,-0.020098,14SEP2004
10002,22NOV2004,0.017509,14SEP2004
10002,23NOV2004,0.006883,14SEP2004
10002,24NOV2004,0.021484,14SEP2004
10002,26NOV2004,0.001912,14SEP2004
10002,29NOV2004,0.001908,14SEP2004
10002,30NOV2004,0.014286,14SEP2004
10002,01DEC2004,0.032394,14SEP2004
10002,02DEC2004,0.005457,14SEP2004
10002,03DEC2004,0.001809,14SEP2004
10002,06DEC2004,-0.038375,14SEP2004
10002,07DEC2004,-0.014084,14SEP2004
10002,08DEC2004,0.035238,14SEP2004
10002,09DEC2004,0.011500,14SEP2004
10002,10DEC2004,0.011824,14SEP2004
10002,13DEC2004,0.022472,14SEP2004
10002,14DEC2004,0.015915,14SEP2004
10002,15DEC2004,-0.001741,14SEP2004
10002,16DEC2004,-0.000436,14SEP2004
10002,17DEC2004,0.003053,14SEP2004
10002,20DEC2004,0.011304,14SEP2004
10002,21DEC2004,0.027515,14SEP2004
10002,22DEC2004,-0.006695,14SEP2004
10002,23DEC2004,-0.024853,14SEP2004
10002,27DEC2004,-0.024190,14SEP2004
10002,28DEC2004,0.060646,14SEP2004
10002,29DEC2004,0.015442,14SEP2004
10002,30DEC2004,-0.002877,14SEP2004
10002,31DEC2004,0.014427,14SEP2004
10002,03JAN2005,-0.031694,14SEP2004
10002,04JAN2005,-0.015946,14SEP2004
10002,05JAN2005,-0.044776,14SEP2004
10002,06JAN2005,-0.046875,14SEP2004
10002,07JAN2005,-0.021077,14SEP2004
10002,10JAN2005,0.005742,14SEP2004
10002,11JAN2005,-0.046622,14SEP2004
10002,12JAN2005,0.012475,14SEP2004
10002,13JAN2005,-0.032528,14SEP2004
10002,14JAN2005,0.008151,14SEP2004
10002,18JAN2005,0.039919,14SEP2004
10002,19JAN2005,-0.029155,14SEP2004
10002,20JAN2005,-0.023523,14SEP2004
10002,21JAN2005,0.021527,14SEP2004
10002,24JAN2005,-0.016558,14SEP2004
10002,25JAN2005,0.000000,14SEP2004
10002,26JAN2005,0.021939,14SEP2004
10002,27JAN2005,-0.032451,14SEP2004
10002,28JAN2005,0.031992,14SEP2004
10002,31JAN2005,0.067500,14SEP2004
10002,01FEB2005,0.016862,14SEP2004
10002,02FEB2005,0.015661,14SEP2004
10002,03FEB2005,0.027211,14SEP2004
10002,04FEB2005,-0.030905,14SEP2004
10002,07FEB2005,0.004556,14SEP2004
10002,08FEB2005,0.002268,14SEP2004
10002,09FEB2005,-0.057014,14SEP2004
10002,10FEB2005,-0.024952,14SEP2004
10002,11FEB2005,0.034941,14SEP2004
10002,14FEB2005,-0.018069,14SEP2004
10002,15FEB2005,0.010170,14SEP2004
10002,16FEB2005,0.023011,14SEP2004
10002,17FEB2005,-0.015933,14SEP2004
10002,18FEB2005,-0.034762,14SEP2004
10002,22FEB2005,-0.055747,14SEP2004
10002,23FEB2005,0.018286,14SEP2004
10002,24FEB2005,0.011801,14SEP2004
10002,25FEB2005,0.029412,14SEP2004
10002,28FEB2005,0.016749,14SEP2004
10002,01MAR2005,-0.003876,14SEP2004
10002,02MAR2005,-0.006809,14SEP2004
10002,03MAR2005,0.022527,14SEP2004
10002,04MAR2005,0.022989,14SEP2004
10002,07MAR2005,-0.016854,14SEP2004
10002,08MAR2005,0.009048,14SEP2004
10002,09MAR2005,-0.010854,14SEP2004
10002,10MAR2005,0.010496,14SEP2004
10002,11MAR2005,0.002361,14SEP2004
10002,14MAR2005,0.023223,14SEP2004
10002,15MAR2005,-0.022696,14SEP2004
10002,16MAR2005,0.004739,14SEP2004
10002,17MAR2005,-0.027123,14SEP2004
10002,18MAR2005,-0.022545,14SEP2004
10002,21MAR2005,-0.018849,14SEP2004
10002,22MAR2005,0.000506,14SEP2004
10002,23MAR2005,-0.008085,14SEP2004
10002,24MAR2005,-0.001528,14SEP2004
10002,28MAR2005,0.007143,14SEP2004
10002,29MAR2005,-0.012665,14SEP2004
10002,30MAR2005,0.036942,14SEP2004
;;;;

[ballardw · Accepted solution]

I am not exactly sure of which date is the "announcement date" or date of interest.

If you are looking for values of date within 5 days MIDDATE in your test data set then add this statement to your test data step and see if you get what you want:

If abs(date-middate) le 5;

You wouldn't actually have to add the variable Middate. You could filter any data with a date valued variable with :

if abs(date - '14SEP2004'd) le 5.

or any other date in Date9 text format.