cumulative abnormal return over a 4-day window

[AmirSari]

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

[PeterClemmensen]

Do you have SAS/ETS?

proc setinit;run;

[AmirSari]

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.

[PeterClemmensen]

How many observations do you have per firm id?

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

[AmirSari]

I have about 2500 observations per firm id.

[PeterClemmensen]

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;

[AmirSari]

Thanks for the code draycut. But it returns missing values for CAR_0_3.

[s_lassen · Accepted solution]

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 @PeterClemmensen's SQL solution. But it is not at all like the result data that you yourself present. How were those calculated?

[AmirSari]

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.