BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AmirSari
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
s_lassen
Meteorite | Level 14

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?

View solution in original post

8 REPLIES 8
PeterClemmensen
Tourmaline | Level 20

Do you have SAS/ETS?

 

proc setinit;run;
AmirSari
Quartz | Level 8
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
Tourmaline | Level 20

How many observations do you have per firm id?

 

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

AmirSari
Quartz | Level 8
I have about 2500 observations per firm id.
PeterClemmensen
Tourmaline | Level 20

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
Quartz | Level 8
Thanks for the code draycut. But it returns missing values for CAR_0_3.
s_lassen
Meteorite | Level 14

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
Quartz | Level 8
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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 3886 views
  • 3 likes
  • 3 in conversation