BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Agent1592
Pyrite | Level 9

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
;;;;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

1 REPLY 1
ballardw
Super User

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 380 views
  • 0 likes
  • 2 in conversation