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 |
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
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
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?
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.
You skip the first two data steps and instead refer to the actual data sets in the DATA and SQL steps.
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?
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: ;
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?
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
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.
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
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;
"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.
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;
"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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.