Calculate abnormal returns for multiple 3-day windows

[Zerg]

Hello all,

I am running into some SAS coding issues on calculating abnormal returns and would appreciate any thoughts/sample code/solution. The data structure looks like:

CUSIP ID	Date(YYYY/MM/DD)	Return
1	1/1/2000	x
1	1/2/2000	x
1	1/3/2000	x
1	1/4/2000	x
1	1/5/2000	x
1	1/8/2000	x
1	1/9/2000	x
1	1/10/2000	x
1	1/11/2000	x
1	1/12/2000	x
…	…	…
1	12/31/2000	x
2	1/1/2000	x
2	1/2/2000	x
2	1/3/2000	x
2	1/4/2000	x
2	1/5/2000	x
2	1/8/2000	x
2	1/9/2000	x
2	1/10/2000	x
2	1/11/2000	x
2	1/12/2000	x
…	…	…
2	12/31/2000	x

What I need is to calculate abnormal returns generated from each CUSIP firm during a 3-day event window. The tricky part is that the abnormal returns for “each non-overlapping 3-day window over the sampling period” need to be calculated.

Taking the above sample data as an example, if I have 249 unique days in year 2000, then there should be 249 / 3 = 83 non-overlapping 3-day windows. So in the output I want to get the abnormal returns of CUSIP firm 1 from 1/1/2000 to 1/3/2000, and then from 1/4/2000 to 1/8/2000 (note that from 1/4/2000 to 1/8/2000 there are only 3 trading days), and so on. And the need for CUSIP firm 2 is the same.

I think the ideal output should look like:

CUSIP ID	Window#	Ab_return
1	1	x
1	2	x
1	3	x
1	…	x
1	83	x
2	1	x
2	2	x
2	3	x
2	…	x
2	83	x

One thing I think worth attention is, in some years, the number of days is not divisible by 3. In that case, event window less than 2-day can be dropped.

Currently, I have codes that calculate abnormal returns for a specified event window:

/*Specify event date*/
data have; set have;
edate = '13apr2005'd;
format edate yymmddn8.;
if not missing(cusip);
run;

/*calculate cumulative abnormal returns CAR*/
%let begdate = -1;
%let enddate = 1;
proc sql undo_policy = none;
create table have_event as select distinct
cusip, edate, exp(sum(log(1+return))) -1 as ab_ret, n(ret) as nobs
from have (where=(date between intnx('WEEKDAY', edate, &begdate) and intnx('WEEKDAY', edate, &enddate)))
group by cusip, edate
order by cusip, edate;
quit;

/*Drop results with less than three days in a window*/
data us2event; set us2event;
if nobs ne 3 then delete;
run;

The formula for the abnormal returns is 

exp(sum(log(1+return))) -1

However, I have difficulties in modifying the codes to meet the requirements I just described. I hope my question makes sense and appreciate any help.

[art297]

Are you trying to do something like the following?:

/* create sum test data */
data have;
  format Date mmddyy10.;
  CUSIP_ID=1;
  do Date='01jan2016'd to '31dec2017'd;
    if weekday(date) in (2,3,4,5,6) then do;;
      Return=ceil(100*rand("uniform") );
      output;
    end;
  end;
  CUSIP_ID=2;
  do Date='01jan2016'd to '31dec2017'd;
    if weekday(date) in (2,3,4,5,6) then do;;
      Return=ceil(50*rand("uniform") );
      output;
    end;
  end;
run;

data need;
  set have;
  by cusip_id;
  if first.cusip_id then counter=1;
  else counter+1;
  year=year(Date);
  oneback=lag(Return);
  twoback=lag2(Return);
  if mod(counter,3) eq 0 then do;
    avg=mean(Return,oneback,twoback);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

[Zerg]

Thank you. It looks like your example codes could do the trick after some modification. I will take a try.

[art297]

Actually, the code should have taken year into account. e.g.:

data need;
  set have;
  by cusip_id;
  year=year(Date);
  yearoneback=lag(year);
  oneback=lag(Return);
  twoback=lag2(Return);
  if first.cusip_id or year ne yearoneback then counter=1;
  else counter+1;
  if mod(counter,3) eq 0 then do;
    avg=mean(Return,oneback,twoback);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

[Ksharp · Accepted solution]

Make a group variable GROUP, after that PROC MEANS by group.

data have;
input #1 CUSIP
#2 Date : mmddyy12.
#3 Return $;
format date mmddyy10.;
cards;
1
1/1/2000
x
1
1/2/2000
x
1
1/3/2000
x
1
1/4/2000
x
1
1/5/2000
x
1
1/8/2000
x
1
1/9/2000
x
1
1/10/2000
x
1
1/11/2000
x
1
1/12/2000
x
1
12/31/2000
x
2
1/1/2000
x
2
1/2/2000
x
2
1/3/2000
x
2
1/4/2000
x
2
1/5/2000
x
2
1/8/2000
x
2
1/9/2000
x
2
1/10/2000
x
2
1/11/2000
x
2
1/12/2000
x
;
run;

data want;
 set have;
 by cusip;
 if first.cusip then n=0;
 n+1;
 if first.cusip or mod(n,3)=1 then group+1;
 drop n;
run;

[Zerg]

Thank you. I incorporated the group variable created by your codes in my sql codes and solved the issue.

proc sql undo_policy = none;
create table abret_2005 as select distinct
cusip, group, exp(sum(log(1+madjret))) -1 as ab_ret, n(group) as nobs
from w3_2005
group by cusip, group
order by cusip, group;
quit;