topic Re: cumulative abnormal return over a 4-day window in SAS Programming

cumulative abnormal return over a 4-day window

AmirSari — Tue, 08 May 2018 04:13:21 GMT

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

Re: cumulative abnormal return over a 4-day window

PeterClemmensen — Tue, 08 May 2018 06:03:08 GMT

Do you have SAS/ETS?

proc setinit;run;

Re: cumulative abnormal return over a 4-day window

AmirSari — Tue, 08 May 2018 06:15:46 GMT

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.

Re: cumulative abnormal return over a 4-day window

PeterClemmensen — Tue, 08 May 2018 08:04:08 GMT

How many observations do you have per firm id?

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

Re: cumulative abnormal return over a 4-day window

AmirSari — Tue, 08 May 2018 08:19:05 GMT

I have about 2500 observations per firm id.

Re: cumulative abnormal return over a 4-day window

PeterClemmensen — Tue, 08 May 2018 08:41:03 GMT

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;

Re: cumulative abnormal return over a 4-day window

s_lassen — Tue, 08 May 2018 11:42:35 GMT

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?

Re: cumulative abnormal return over a 4-day window

AmirSari — Wed, 09 May 2018 17:44:47 GMT

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

Re: cumulative abnormal return over a 4-day window

AmirSari — Wed, 09 May 2018 17:59:23 GMT

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.