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

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/20040.001566486-0.0207041980.0043460610.006833362
30/11/2004-0.0207839530.0262962060.0084073120.003934506
01/12/2004-0.0043566030.0355291230.0347538360.001334674
02/12/2004-0.0102504270.0169724460.0021573270.008849479
03/12/2004-0.000154985-0.008940174-0.0022758770.000195348
06/12/20040.0105010150.261085532-0.031182369-0.00063553
07/12/2004-0.021693979-0.0538856930.0084346730.010072289
08/12/20040.009444757-0.013386605-0.0050205820.00032525
09/12/2004-0.0187073740.015054951-0.0047859280.003669762
10/12/20040.0115975390.040542795-0.001771166-0.003909327
13/12/2004-0.021790463-0.0059682920.017389206-0.00123946
14/12/20040.0182702610.020703078-0.000736595-0.001693578
15/12/2004-0.014434544-0.029766612-0.0107334930.017737698

This is the dataset, named trading_obs

insider         transaction

Relation            Type tradedate year     Code

CFOSell200412072004US7750431022
CEOSell200412082004US7750431022
CEOSell200511292005US7750431022
CEOSell200612072006US7750431022
CEOBuy200811182008US7750431022
President/CEOSell201012022010US7750431022
President/CEOSell201112022011US7750431022
President/CEOSell201202282012US7750431022
President/CEOSell201202292012US7750431022
President/CEOSell201212062012US7750431022
CEOSell200407272004US5801351017
CEOSell200505022005US5801351017
CEOSell200509122005US5801351017
CEOSell200602092006US5801351017
CEOSell200603172006US5801351017
CEOSell200609132006US5801351017
CEOSell200612122006US5801351017
CEOSell200710232007US5801351017
CEOSell200711302007US5801351017
CEOBuy200810232008US5801351017
CEOSell200903232009US5801351017
CEOSell200905122009US5801351017
CEOSell201002122010US5801351017
CEOSell201010222010US5801351017
CEOSell201102112011US5801351017
CEOSell201104252011US5801351017
CEOSell201202102012US5801351017
CEOSell200410222004US5801351017
CFOBuy200610312006US45170X1063
CFOSell200409132004US0394831020
CFOSell200409142004US0394831020
CFOSell200411182004US0394831020
CFOSell200412302005US0394831020
CFOSell200508082005US0394831020
CFOSell200508192005US0394831020
CFOSell200602222006US0394831020
CFOSell200605102006US0394831020
CFOSell200610132006US0394831020
CFOSell200703232007US0394831020
CFOSell200708172007US0394831020
CFOSell200808082008US0394831020
CFOSell200902102009US0394831020
CFOSell200908102009US0394831020
CFOSell201004262010US0394831020
CFOSell201008032010US0394831020
CFOSell201102242011US0394831020
CFOSell201102252011US0394831020
CFOSell201108082011US0394831020
CFOSell201111012011US0394831020
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

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

View solution in original post

23 REPLIES 23
Ksharp
Super User

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

AbuChowdhury
Fluorite | Level 6

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?

AbuChowdhury
Fluorite | Level 6

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.

Reeza
Super User

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

AbuChowdhury
Fluorite | Level 6

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?

AbuChowdhury
Fluorite | Level 6

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

AbuChowdhury
Fluorite | Level 6

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?

Ksharp
Super User

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

AbuChowdhury
Fluorite | Level 6

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.

Ksharp
Super User

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

AbuChowdhury
Fluorite | Level 6

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;

Ksharp
Super User

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

AbuChowdhury
Fluorite | Level 6

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;

Ksharp
Super User

"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

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
  • 23 replies
  • 7525 views
  • 6 likes
  • 3 in conversation