topic Re: Cumulative Abnormal Return Calculations in SAS Programming

Cumulative Abnormal Return Calculations

AbuChowdhury — Sat, 10 May 2014 11:27:58 GMT

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.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

This is the dataset, named trading_obs

insider transaction

Relation Type tradedate year Code

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

Re: Cumulative Abnormal Return Calculations

Ksharp — Sat, 10 May 2014 14:22:42 GMT

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;

data  trading_obs;
infile cards expandtabs;
input Relation : $10.   type $       tradedate : yymmdd10.  year     Code : $20.;
format  tradedate  yymmddn8.;
cards;
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
;
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
   from trading_obs as a;
quit;

Xia Keshan

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sat, 10 May 2014 15:20:36 GMT

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?

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sat, 10 May 2014 16:57:27 GMT

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.

Re: Cumulative Abnormal Return Calculations

Reeza — Sat, 10 May 2014 21:55:19 GMT

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

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sat, 10 May 2014 23:15:04 GMT

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: ;

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sat, 10 May 2014 23:24:32 GMT

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?

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sun, 11 May 2014 01:53:26 GMT

Hi,

I ran the following codes as well but got no results:

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

from trading_obs as a;

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?

Re: Cumulative Abnormal Return Calculations

Ksharp — Sun, 11 May 2014 06:03:46 GMT

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

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sun, 11 May 2014 09:38:36 GMT

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.

Re: Cumulative Abnormal Return Calculations

Ksharp — Sun, 11 May 2014 12:24:46 GMT

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

libname x v9 'c:\temp';
data new;
 set x.trading_obs;
 new_tradedate=input(strip(tradedate),yymmdd10.);
 format new_tradedate yymmdd10.;
run;

Xia Keshan

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sun, 11 May 2014 13:03:13 GMT

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

from trading_obs as a;

quit;

Re: Cumulative Abnormal Return Calculations

Ksharp — Sun, 11 May 2014 13:39:48 GMT

"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.

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Sun, 11 May 2014 14:33:21 GMT

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

from trading_obs as a;

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;

Re: Cumulative Abnormal Return Calculations

Ksharp — Mon, 12 May 2014 11:53:28 GMT

"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

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Mon, 12 May 2014 12:02:19 GMT

Thank you very much for all. I will run the codes and let you know.

Abu Chowdhury

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Mon, 19 May 2014 15:05:08 GMT

Hi,

I submitted your proc sql codes but it's taking very long time. For example, it takes more than two hours to calculate the CAR for different windows (such as (0,65) days, (66,130) days etc.) when the dataset has 2502 event days (tradedate). I have another dataset where there are approximately 35,000 event days, and it took more than 40 hours to get the CARs ! Is it normal for taking such long time or is there something wrong?

In the attached file, I calculated the CARs for different windows using your codes. For each firm (the variable 'Code' ) I need to have only one CAR for each year. For example, if the same firm has multiple CARs in a specific year because of the multiple tradedates, the average (or sum) of those CARs for that year needs to be calculated. It means, for each firm for each year there should be one CAR value. How can I do that?

Abu Chowdhury

Re: Cumulative Abnormal Return Calculations

Ksharp — Tue, 20 May 2014 12:31:34 GMT

You can make three index for code, date , tradedate respectively . Maybe That would fast you.

And about more fast way, what I could think is using Hash Table , But that is a long story to tell.

Xia Keshan

Re: Cumulative Abnormal Return Calculations

AbuChowdhury — Tue, 20 May 2014 12:41:57 GMT

Thanks but I did not understand how to make index. I will be grateful if you tell me.

I had the second query:

Waiting for your reply.

Re: Cumulative Abnormal Return Calculations

Ksharp — Tue, 20 May 2014 14:04:17 GMT

data class;
 set sashelp.class;
run;
proc datasets library=work;
 modify class;
  index create age;
quit;

Start a new topic, and let others knows your problem, I can't get you .

Xia Keshan