Fluorite | Level 6

## Cumulative Abnormal Return Calculations

Hi,

I need to calculate the cumulative abnormal returns (CAR). I include the portion of two datasets. The abnormal returns of firms ( US7750431022  US45170X1063 US0394831020 US5801351017) are in dataset 1 and the event days (tradedate) are in the dataset 2. In dataset 2, the variable 'Code' refers to the firms. I have many more firms and many more event days in original datasets.

I need to calculate CAR from the abnormal returns from dataset 1 and tradedate from dataset 2. There are different event days (tradedate) for different firms. CAR will have to calculated for (+65 days) window, (66 days to 130 days) window, and (-10 days, +65 days) window.

How can I calculate CAR for those firms in those tradedates?

Abu

Dataset 1: This is the dataset for abnormal returns, named abret.

Date          US7750431022  US45170X1063 US0394831020 US5801351017

 29/11/2004 0.00156649 -0.0207042 0.00434606 0.00683336 30/11/2004 -0.020784 0.0262962 0.00840731 0.00393451 01/12/2004 -0.0043566 0.0355291 0.0347538 0.00133467 02/12/2004 -0.0102504 0.0169724 0.00215733 0.00884948 03/12/2004 -0.000154985 -0.00894017 -0.00227588 0.000195348 06/12/2004 0.010501 0.261086 -0.0311824 -0.00063553 07/12/2004 -0.021694 -0.0538857 0.00843467 0.0100723 08/12/2004 0.00944476 -0.0133866 -0.00502058 0.00032525 09/12/2004 -0.0187074 0.015055 -0.00478593 0.00366976 10/12/2004 0.0115975 0.0405428 -0.00177117 -0.00390933 13/12/2004 -0.0217905 -0.00596829 0.0173892 -0.00123946 14/12/2004 0.0182703 0.0207031 -0.000736595 -0.00169358 15/12/2004 -0.0144345 -0.0297666 -0.0107335 0.0177377

This is the dataset, named trading_obs

insider         transaction

 CFO Sell 20041207 2004 US7750431022 CEO Sell 20041208 2004 US7750431022 CEO Sell 20051129 2005 US7750431022 CEO Sell 20061207 2006 US7750431022 CEO Buy 20081118 2008 US7750431022 President/CEO Sell 20101202 2010 US7750431022 President/CEO Sell 20111202 2011 US7750431022 President/CEO Sell 20120228 2012 US7750431022 President/CEO Sell 20120229 2012 US7750431022 President/CEO Sell 20121206 2012 US7750431022 CEO Sell 20040727 2004 US5801351017 CEO Sell 20050502 2005 US5801351017 CEO Sell 20050912 2005 US5801351017 CEO Sell 20060209 2006 US5801351017 CEO Sell 20060317 2006 US5801351017 CEO Sell 20060913 2006 US5801351017 CEO Sell 20061212 2006 US5801351017 CEO Sell 20071023 2007 US5801351017 CEO Sell 20071130 2007 US5801351017 CEO Buy 20081023 2008 US5801351017 CEO Sell 20090323 2009 US5801351017 CEO Sell 20090512 2009 US5801351017 CEO Sell 20100212 2010 US5801351017 CEO Sell 20101022 2010 US5801351017 CEO Sell 20110211 2011 US5801351017 CEO Sell 20110425 2011 US5801351017 CEO Sell 20120210 2012 US5801351017 CEO Sell 20041022 2004 US5801351017 CFO Buy 20061031 2006 US45170X1063 CFO Sell 20040913 2004 US0394831020 CFO Sell 20040914 2004 US0394831020 CFO Sell 20041118 2004 US0394831020 CFO Sell 20041230 2005 US0394831020 CFO Sell 20050808 2005 US0394831020 CFO Sell 20050819 2005 US0394831020 CFO Sell 20060222 2006 US0394831020 CFO Sell 20060510 2006 US0394831020 CFO Sell 20061013 2006 US0394831020 CFO Sell 20070323 2007 US0394831020 CFO Sell 20070817 2007 US0394831020 CFO Sell 20080808 2008 US0394831020 CFO Sell 20090210 2009 US0394831020 CFO Sell 20090810 2009 US0394831020 CFO Sell 20100426 2010 US0394831020 CFO Sell 20100803 2010 US0394831020 CFO Sell 20110224 2011 US0394831020 CFO Sell 20110225 2011 US0394831020 CFO Sell 20110808 2011 US0394831020 CFO Sell 20111101 2011 US0394831020
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Cumulative Abnormal Return Calculations

If I understand what you mean.

```data Dataset1;
infile cards expandtabs;
input Date   : ddmmyy10.       US7750431022  US45170X1063 US0394831020 US5801351017 ;
format date ddmmyy10.;
cards;
29/11/2004     0.001566486     -0.020704198     0.004346061     0.006833362
30/11/2004     -0.020783953     0.026296206     0.008407312     0.003934506
01/12/2004     -0.004356603     0.035529123     0.034753836     0.001334674
02/12/2004     -0.010250427     0.016972446     0.002157327     0.008849479
03/12/2004     -0.000154985     -0.008940174     -0.002275877     0.000195348
06/12/2004     0.010501015     0.261085532     -0.031182369     -0.00063553
07/12/2004     -0.021693979     -0.053885693     0.008434673     0.010072289
08/12/2004     0.009444757     -0.013386605     -0.005020582     0.00032525
09/12/2004     -0.018707374     0.015054951     -0.004785928     0.003669762
10/12/2004     0.011597539     0.040542795     -0.001771166     -0.003909327
13/12/2004     -0.021790463     -0.005968292     0.017389206     -0.00123946
14/12/2004     0.018270261     0.020703078     -0.000736595     -0.001693578
15/12/2004     -0.014434544     -0.029766612     -0.010733493     0.017737698
;
run;

infile cards expandtabs;
input Relation : \$10.   type \$       tradedate : yymmdd10.  year     Code : \$20.;
cards;
CFO     Sell     20041207     2004     US7750431022
CEO     Sell     20041208     2004     US7750431022
CEO     Sell     20051129     2005     US7750431022
CEO     Sell     20061207     2006     US7750431022
President/CEO   Sell     20101202     2010     US7750431022
President/CEO     Sell     20111202     2011     US7750431022
President/CEO     Sell     20120228     2012     US7750431022
President/CEO     Sell     20120229     2012     US7750431022
President/CEO     Sell     20121206     2012     US7750431022
CEO     Sell     20040727     2004     US5801351017
CEO     Sell     20050502     2005     US5801351017
CEO     Sell     20050912     2005     US5801351017
CEO     Sell     20060209     2006     US5801351017
CEO     Sell     20060317     2006     US5801351017
CEO     Sell     20060913     2006     US5801351017
CEO     Sell     20061212     2006     US5801351017
CEO     Sell     20071023     2007     US5801351017
CEO     Sell     20071130     2007     US5801351017
CEO     Sell     20090323     2009     US5801351017
CEO     Sell     20090512     2009     US5801351017
CEO     Sell     20100212     2010     US5801351017
CEO     Sell     20101022     2010     US5801351017
CEO     Sell     20110211     2011     US5801351017
CEO     Sell     20110425     2011     US5801351017
CEO     Sell     20120210     2012     US5801351017
CEO     Sell     20041022     2004     US5801351017
CFO     Sell     20040913     2004     US0394831020
CFO     Sell     20040914     2004     US0394831020
CFO     Sell     20041118     2004     US0394831020
CFO     Sell     20041230     2005     US0394831020
CFO     Sell     20050808     2005     US0394831020
CFO     Sell     20050819     2005     US0394831020
CFO     Sell     20060222     2006     US0394831020
CFO     Sell     20060510     2006     US0394831020
CFO     Sell     20061013     2006     US0394831020
CFO     Sell     20070323     2007     US0394831020
CFO     Sell     20070817     2007     US0394831020
CFO     Sell     20080808     2008     US0394831020
CFO     Sell     20090210     2009     US0394831020
CFO     Sell     20090810     2009     US0394831020
CFO     Sell     20100426     2010     US0394831020
CFO     Sell     20100803     2010     US0394831020
CFO     Sell     20110224     2011     US0394831020
CFO     Sell     20110225     2011     US0394831020
CFO     Sell     20110808     2011     US0394831020
CFO     Sell     20111101     2011     US0394831020
;
run;
data x;
set dataset1;
length code \$ 40;
array a{*} us: ;
do i=1 to dim(a);
code=vname(a{i});
value=a{i};
output;
end;
keep date code value;
run;
proc sql;
create table want as
select a.*,(select sum(value) from x where code=a.code and date between tradedate and tradedate+65) as _65,
(select sum(value) from x where code=a.code and date between tradedate+66 and tradedate+130) as _66_130,
(select sum(value) from x where code=a.code and date between tradedate-10 and tradedate+65) as _10_65
quit;

```

Xia Keshan

23 REPLIES 23
Super User

## Re: Cumulative Abnormal Return Calculations

If I understand what you mean.

```data Dataset1;
infile cards expandtabs;
input Date   : ddmmyy10.       US7750431022  US45170X1063 US0394831020 US5801351017 ;
format date ddmmyy10.;
cards;
29/11/2004     0.001566486     -0.020704198     0.004346061     0.006833362
30/11/2004     -0.020783953     0.026296206     0.008407312     0.003934506
01/12/2004     -0.004356603     0.035529123     0.034753836     0.001334674
02/12/2004     -0.010250427     0.016972446     0.002157327     0.008849479
03/12/2004     -0.000154985     -0.008940174     -0.002275877     0.000195348
06/12/2004     0.010501015     0.261085532     -0.031182369     -0.00063553
07/12/2004     -0.021693979     -0.053885693     0.008434673     0.010072289
08/12/2004     0.009444757     -0.013386605     -0.005020582     0.00032525
09/12/2004     -0.018707374     0.015054951     -0.004785928     0.003669762
10/12/2004     0.011597539     0.040542795     -0.001771166     -0.003909327
13/12/2004     -0.021790463     -0.005968292     0.017389206     -0.00123946
14/12/2004     0.018270261     0.020703078     -0.000736595     -0.001693578
15/12/2004     -0.014434544     -0.029766612     -0.010733493     0.017737698
;
run;

infile cards expandtabs;
input Relation : \$10.   type \$       tradedate : yymmdd10.  year     Code : \$20.;
cards;
CFO     Sell     20041207     2004     US7750431022
CEO     Sell     20041208     2004     US7750431022
CEO     Sell     20051129     2005     US7750431022
CEO     Sell     20061207     2006     US7750431022
President/CEO   Sell     20101202     2010     US7750431022
President/CEO     Sell     20111202     2011     US7750431022
President/CEO     Sell     20120228     2012     US7750431022
President/CEO     Sell     20120229     2012     US7750431022
President/CEO     Sell     20121206     2012     US7750431022
CEO     Sell     20040727     2004     US5801351017
CEO     Sell     20050502     2005     US5801351017
CEO     Sell     20050912     2005     US5801351017
CEO     Sell     20060209     2006     US5801351017
CEO     Sell     20060317     2006     US5801351017
CEO     Sell     20060913     2006     US5801351017
CEO     Sell     20061212     2006     US5801351017
CEO     Sell     20071023     2007     US5801351017
CEO     Sell     20071130     2007     US5801351017
CEO     Sell     20090323     2009     US5801351017
CEO     Sell     20090512     2009     US5801351017
CEO     Sell     20100212     2010     US5801351017
CEO     Sell     20101022     2010     US5801351017
CEO     Sell     20110211     2011     US5801351017
CEO     Sell     20110425     2011     US5801351017
CEO     Sell     20120210     2012     US5801351017
CEO     Sell     20041022     2004     US5801351017
CFO     Sell     20040913     2004     US0394831020
CFO     Sell     20040914     2004     US0394831020
CFO     Sell     20041118     2004     US0394831020
CFO     Sell     20041230     2005     US0394831020
CFO     Sell     20050808     2005     US0394831020
CFO     Sell     20050819     2005     US0394831020
CFO     Sell     20060222     2006     US0394831020
CFO     Sell     20060510     2006     US0394831020
CFO     Sell     20061013     2006     US0394831020
CFO     Sell     20070323     2007     US0394831020
CFO     Sell     20070817     2007     US0394831020
CFO     Sell     20080808     2008     US0394831020
CFO     Sell     20090210     2009     US0394831020
CFO     Sell     20090810     2009     US0394831020
CFO     Sell     20100426     2010     US0394831020
CFO     Sell     20100803     2010     US0394831020
CFO     Sell     20110224     2011     US0394831020
CFO     Sell     20110225     2011     US0394831020
CFO     Sell     20110808     2011     US0394831020
CFO     Sell     20111101     2011     US0394831020
;
run;
data x;
set dataset1;
length code \$ 40;
array a{*} us: ;
do i=1 to dim(a);
code=vname(a{i});
value=a{i};
output;
end;
keep date code value;
run;
proc sql;
create table want as
select a.*,(select sum(value) from x where code=a.code and date between tradedate and tradedate+65) as _65,
(select sum(value) from x where code=a.code and date between tradedate+66 and tradedate+130) as _66_130,
(select sum(value) from x where code=a.code and date between tradedate-10 and tradedate+65) as _10_65
quit;

```

Xia Keshan

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Hi,

Thanks for your reply. I will try with those codes. Is it possible to do this job by using proc expand such as:

proc expand data = dataset1 out = ma;

convert US7750431022 = US7750431022_ma / transformout=( reverse movave 65 reverse );

run;

The 'Date' and 'tradedate' variables of these two sets are in different format. How can I convert 'Date' variable of the Dataset1 in the 'tradedate' format?

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Hi,

My data are already in the datasets, so I dont want to copy the data in the codes. Then should I write the following codes?

data Dataset1;

infile cards expandtabs;

input Date   : ddmmyy10.       US7750431022  US45170X1063 US0394831020 US5801351017 ;

format date ddmmyy10.;

cards;

If I write these, it shows error message.

Super User

## Re: Cumulative Abnormal Return Calculations

You skip the first two data steps and instead refer to the actual data sets in the DATA and SQL steps.

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Thanks for your reply. I did that and submitted the data steps 5 hours before, but it is still running !!!

I think the last line in the following codes should be changed. At first, I gave four firm names that started with US  ( for example,US7750431022  US45170X1063 US0394831020 US5801351017). But in my original datasets there are many other firms whose names start with other letters (such as, CH123456, CZ123456, ANN123456  etc.). So should I write: array a{*} us: ;  ?

data x;

set dataset1;

length code \$ 40;

array a{*} us: ;

And the date formats, Date in dataset1 (such as 01/12/2004) and tradedate in trading_obs dataset (such as 20041201), are different. tradedate in trading_obs dataset are event days. Should I have the same format of the dates for both 'Date' and 'tradedate' of those datasets?

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Thanks for your reply. I did that and submitted the data steps 5 hours before, but it is still running !!!

I think the last line in the following codes should be changed. At first, I gave four firm names that started with US  ( for example, US7750431022  US45170X1063 US0394831020 US5801351017). But in my original datasets there are many other firms whose names start with other letters (such as, CH123456, CZ123456 etc.). So should I write: array a{*} us: ;  ?

data x;

set dataset1;

length code \$ 40;

array a{*} us: ;

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Hi,

proc sql;

create table want as

select a.*,(select sum(value) from x where code=a.code and date between tradedate and tradedate+65) as _65

quit;

In the 'want' table, a variable named -65 is created, but the values of this variable are blank. Is it because of the format of the 'tradedate' in the trading_obs dataset? I wanted to change the format of this tradedate variable but it shows the following message in the log:

NOTE: Table has been opened in browse mode.

ERROR: There was a problem with the format so BEST. was used.

What should I do now?

Super User

## Re: Cumulative Abnormal Return Calculations

You can change the date format in dataset1 as :

data Dataset1;

infile cards expandtabs;

input Date   : ddmmyy10.       US7750431022  US45170X1063 US0394831020 US5801351017 ;

format date yymmddn8.;

And I suggest you firstly use a small table to test the code and see if the result is what you need.

And you'd better post some sample SAS dataset , and let us to find where is problem .

You said you have already run 5+ hours long, how big is your table?

You can add any number of variables you need into array  as: (if they start with us... cz... cs....)

array a{*} us: cz: cs: ;  ?

Xia Keshan

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Problem is in the 'tradedate' variable of the trading_obs dataset, I think. I changed the date format of this variable in Excel and then imported again to SAS. Then I ran your codes and your codes ran absolutely fine. I got the 'want' table very quickly as well. I am attaching the two datasets, then please take a look at the 'tradedate' variable of the trading_obs dataset.

Super User

## Re: Cumulative Abnormal Return Calculations

Oh. Your tradedate is numeric type variable not date type variable. Here you change it as :

```libname x v9 'c:\temp';
data new;
run;

```

Xia Keshan

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

Thanks, I will try this and let you know.

I have two more questions. These two are separate questions:

1. After running your codes, I get the CAR for (0,65) window for each tradedate. There are 2502 tradedate variables in the trading_obs dataset that I provided. So there will be 2502 CARs.  If I want to calculate the average of these 2502 CARs, then what should I do?

2. If I would like to calculate the average value of the (0,65) window, for example, then should I write:

proc sql;

create table want as

select a.*,(select mean(value) from x where code=a.code and date between tradedate and tradedate+65) as _65

quit;

Super User

## Re: Cumulative Abnormal Return Calculations

"1. After running your codes, I get the CAR for (0,65) window for each tradedate. "

Assuming these 2502 variables named as car1 car2 .........car2052 , use  mean=mean(of car1-car2052);

"2. If I would like to calculate the average value of the (0,65) window,"

Yes. You got it.

Fluorite | Level 6

## Re: Cumulative Abnormal Return Calculations

I got the fist CAR value as -0.197547267 and last car value as -1.112727748. So should I write like this?

proc sql;

create table want as

select a.*,(select sum(value) from x where code=a.code and date between tradedate and tradedate+65) as _65

mean = mean(of -0.197547267 - (-1.112727748));

quit;

Please correct me. Again can I also use the following codes:

proc univariate data = want;

var _65;

run;

Super User

## Re: Cumulative Abnormal Return Calculations

"I got the fist CAR value as -0.197547267 and last car value as -1.112727748. So should I write like this?"

No.you said you have 2052 tradedate variables , each CAR corresponding to each tradedate like car1 car2 ....

and code this :   mean=mean(of car1-car2052);

"Please correct me. Again can I also use the following codes:"

Yes. you can.

Xia Keshan

Discussion stats
• 23 replies
• 8422 views
• 6 likes
• 3 in conversation