Calculate abnormal returns for multiple 3-day windows

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 9
Accepted Solution

Calculate abnormal returns for multiple 3-day windows

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.

 


Accepted Solutions
Solution
‎04-02-2018 12:46 PM
Super User
Posts: 10,778

Re: Calculate abnormal returns for multiple 3-day windows

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;

View solution in original post


All Replies
PROC Star
Posts: 8,164

Re: Calculate abnormal returns for multiple 3-day windows

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

 

Occasional Contributor
Posts: 9

Re: Calculate abnormal returns for multiple 3-day windows

Thank you. It looks like your example codes could do the trick after some modification. I will take a try.
PROC Star
Posts: 8,164

Re: Calculate abnormal returns for multiple 3-day windows

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

 

Solution
‎04-02-2018 12:46 PM
Super User
Posts: 10,778

Re: Calculate abnormal returns for multiple 3-day windows

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;
Occasional Contributor
Posts: 9

Re: Calculate abnormal returns for multiple 3-day windows

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 220 views
  • 0 likes
  • 3 in conversation