Solved
Contributor
Posts: 44

# cumulative abnormal return over a 4-day window

Dear All,

I am having trouble calculating cumulative abnormal return over a 4-day window. I have daily abnormal stock returns for over 2000 firms. I would like to create a variable for cumulative abnormal stock returns over a moving 4-day (0, +3) window using the following formula:

exp(sum(log(1+AbReturn))) – 1

Here is a sample of my dataset:

firmID        date         AbReturn

10000  19860107      -0.01380893

10000  19860108      -0.003645893

10000  19860109      0.01121904

10000  19860110      -0.00008338

10000  19860113      0.047250921

10000  19860114      0.047252809

10000  19860115      0.037248681

10000  19860116      0.038776713

10000  19860117      0.001741175

.

.

10001  19860109      0.01121904

10001  19860110      0.021655751

10001  19860113      -0.00274908

10001  19860114      -0.00036624

10001  19860115      0.023709028

10001  19860116      0.026226286

10001  19860117      0.001741175

10001  19860120      0.013734809

.

.

The desired dataset will look like this:

firmID       date                   AbReturn            CAR_(0,+3)

10000    19860107            -0.01380893       -0.002812232

10000    19860108            -0.003645893     0.023546433

10000    19860109            0.01121904         0.045935092

10000    19860110            -0.00008338       0.057543709

10000    19860113            0.047250921      0.075200742

10000    19860114            0.047252809      0.054653372

10000    19860115            0.037248681

10000    19860116            0.038776713

10000    19860117            0.001741175

.

.

10001    19860109            0.01121904         0.012877391

10001    19860110            0.021655751      0.01829176

10001    19860113            -0.00274908       0.020267684

10001    19860114            -0.00036624       0.022260251

10001    19860115            0.023709028      0.028498069

10001    19860116            0.026226286      0.0180846

10001    19860117            0.001741175      0.006702245

10001    19860120            0.013734809      0.005941943

.

.

Any help is greatly appreciated.

Amir

Accepted Solutions
Solution
‎05-09-2018 01:59 PM
PROC Star
Posts: 275

## Re: cumulative abnormal return over a 4-day window

The data that you describe can be generated in a data step like this:

data want;

set have nobs=nobs;

CAR=log(sum(1,AbReturn));

do _N_=_N_+1 to nobs;

set have(rename=(FirmID=NextID Date=NextDate AbReturn=NextReturn)) point=_N_;

if FirmID ne NextID then leave;

if NextDate>Date+3 then leave;

CAR+log(sum(1,NextReturn));

end;

CAR=exp(Car)-1;

drop Next:;

run;

(the data should be sorted by FirmID and Date)

This gives the same output as @draycut's SQL solution. But it is not at all like the result data that you yourself present. How were those calculated?

All Replies
PROC Star
Posts: 1,400

## Re: cumulative abnormal return over a 4-day window

[ Edited ]

Do you have SAS/ETS?

``proc setinit;run;``
Contributor
Posts: 44

## Re: cumulative abnormal return over a 4-day window

Yes, I do have SAS/ETS. I could figure out a way to get the returns using proc expand and lead function, but I will be happy if you have a more elegant solution.
PROC Star
Posts: 1,400

## Re: cumulative abnormal return over a 4-day window

How many observations do you have per firm id?

This can be done in PROC SQL for decent sized tables..

Contributor
Posts: 44

## Re: cumulative abnormal return over a 4-day window

I have about 2500 observations per firm id.
PROC Star
Posts: 1,400

## Re: cumulative abnormal return over a 4-day window

I recommend that you go with the PROC EXPAND approach. Makes it much easier to control when values should be missing and such.

You can do something like this in PROC SQL, but it is not very efficient for large data sets...

``````proc sql;
create table want as
select *,
(select (exp(sum(log(1+AbReturn)))-1) from have
where a.date <= date <= intnx('day',a.date,3,'s') and FirmID=a.FirmID) as CAR_0_3
from have as a;
quit;``````
Contributor
Posts: 44

## Re: cumulative abnormal return over a 4-day window

Thanks for the code draycut. But it returns missing values for CAR_0_3.
Solution
‎05-09-2018 01:59 PM
PROC Star
Posts: 275

## Re: cumulative abnormal return over a 4-day window

The data that you describe can be generated in a data step like this:

data want;

set have nobs=nobs;

CAR=log(sum(1,AbReturn));

do _N_=_N_+1 to nobs;

set have(rename=(FirmID=NextID Date=NextDate AbReturn=NextReturn)) point=_N_;

if FirmID ne NextID then leave;

if NextDate>Date+3 then leave;

CAR+log(sum(1,NextReturn));

end;

CAR=exp(Car)-1;

drop Next:;

run;

(the data should be sorted by FirmID and Date)

This gives the same output as @draycut's SQL solution. But it is not at all like the result data that you yourself present. How were those calculated?

Contributor
Posts: 44

## Re: cumulative abnormal return over a 4-day window

Thanks @s_lassen. This code gives me what I want. The reason my output is different is because I used log function in excel to generate those values.
☑ This topic is solved.