## Calculate abnormal returns for multiple 3-day windows

Solved
Occasional Contributor
Posts: 9

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

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.