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

Hello all,

 

I am running into some SAS coding issues on calculating abnormal returns and would appreciate any thoughts/sample code/solution. The data structure looks like:

 

CUSIP ID

Date(YYYY/MM/DD)

Return

1

1/1/2000

x

1

1/2/2000

x

1

1/3/2000

x

1

1/4/2000

x

1

1/5/2000

x

1

1/8/2000

x

1

1/9/2000

x

1

1/10/2000

x

1

1/11/2000

x

1

1/12/2000

x

1

12/31/2000

x

2

1/1/2000

x

2

1/2/2000

x

2

1/3/2000

x

2

1/4/2000

x

2

1/5/2000

x

2

1/8/2000

x

2

1/9/2000

x

2

1/10/2000

x

2

1/11/2000

x

2

1/12/2000

x

2

12/31/2000

x

 

What I need is to calculate abnormal returns generated from each CUSIP firm during a 3-day event window. The tricky part is that the abnormal returns for “each non-overlapping 3-day window over the sampling period” need to be calculated.

 

Taking the above sample data as an example, if I have 249 unique days in year 2000, then there should be 249 / 3 = 83 non-overlapping 3-day windows. So in the output I want to get the abnormal returns of CUSIP firm 1 from 1/1/2000 to 1/3/2000, and then from 1/4/2000 to 1/8/2000 (note that from 1/4/2000 to 1/8/2000 there are only 3 trading days), and so on. And the need for CUSIP firm 2 is the same.

 

I think the ideal output should look like:

CUSIP ID

Window#

Ab_return

1

1

x

1

2

x

1

3

x

1

x

1

83

x

2

1

x

2

2

x

2

3

x

2

x

2

83

x

 

One thing I think worth attention is, in some years, the number of days is not divisible by 3. In that case, event window less than 2-day can be dropped.

 

Currently, I have codes that calculate abnormal returns for a specified event window:

/*Specify event date*/
data have; set have;
edate = '13apr2005'd;
format edate yymmddn8.;
if not missing(cusip);
run;

/*calculate cumulative abnormal returns CAR*/
%let begdate = -1;
%let enddate = 1;
proc sql undo_policy = none;
create table have_event as select distinct
cusip, edate, exp(sum(log(1+return))) -1 as ab_ret, n(ret) as nobs
from have (where=(date between intnx('WEEKDAY', edate, &begdate) and intnx('WEEKDAY', edate, &enddate)))
group by cusip, edate
order by cusip, edate;
quit;

/*Drop results with less than three days in a window*/
data us2event; set us2event;
if nobs ne 3 then delete;
run;

The formula for the abnormal returns is 

exp(sum(log(1+return))) -1

 

However, I have difficulties in modifying the codes to meet the requirements I just described. I hope my question makes sense and appreciate any help.

 

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User

Make a group variable GROUP, after that PROC MEANS by group.

 

data have;
input #1 CUSIP
#2 Date : mmddyy12.
#3 Return $;
format date mmddyy10.;
cards;
1
1/1/2000
x
1
1/2/2000
x
1
1/3/2000
x
1
1/4/2000
x
1
1/5/2000
x
1
1/8/2000
x
1
1/9/2000
x
1
1/10/2000
x
1
1/11/2000
x
1
1/12/2000
x
1
12/31/2000
x
2
1/1/2000
x
2
1/2/2000
x
2
1/3/2000
x
2
1/4/2000
x
2
1/5/2000
x
2
1/8/2000
x
2
1/9/2000
x
2
1/10/2000
x
2
1/11/2000
x
2
1/12/2000
x
;
run;

data want;
 set have;
 by cusip;
 if first.cusip then n=0;
 n+1;
 if first.cusip or mod(n,3)=1 then group+1;
 drop n;
run;

View solution in original post

5 REPLIES 5
art297
Opal | Level 21

Are you trying to do something like the following?:

/* create sum test data */
data have;
  format Date mmddyy10.;
  CUSIP_ID=1;
  do Date='01jan2016'd to '31dec2017'd;
    if weekday(date) in (2,3,4,5,6) then do;;
      Return=ceil(100*rand("uniform") );
      output;
    end;
  end;
  CUSIP_ID=2;
  do Date='01jan2016'd to '31dec2017'd;
    if weekday(date) in (2,3,4,5,6) then do;;
      Return=ceil(50*rand("uniform") );
      output;
    end;
  end;
run;

data need;
  set have;
  by cusip_id;
  if first.cusip_id then counter=1;
  else counter+1;
  year=year(Date);
  oneback=lag(Return);
  twoback=lag2(Return);
  if mod(counter,3) eq 0 then do;
    avg=mean(Return,oneback,twoback);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

Zerg
Calcite | Level 5
Thank you. It looks like your example codes could do the trick after some modification. I will take a try.
art297
Opal | Level 21

Actually, the code should have taken year into account. e.g.:

data need;
  set have;
  by cusip_id;
  year=year(Date);
  yearoneback=lag(year);
  oneback=lag(Return);
  twoback=lag2(Return);
  if first.cusip_id or year ne yearoneback then counter=1;
  else counter+1;
  if mod(counter,3) eq 0 then do;
    avg=mean(Return,oneback,twoback);
    output;
  end;
run;

Art, CEO, AnalystFinder.com

 

Ksharp
Super User

Make a group variable GROUP, after that PROC MEANS by group.

 

data have;
input #1 CUSIP
#2 Date : mmddyy12.
#3 Return $;
format date mmddyy10.;
cards;
1
1/1/2000
x
1
1/2/2000
x
1
1/3/2000
x
1
1/4/2000
x
1
1/5/2000
x
1
1/8/2000
x
1
1/9/2000
x
1
1/10/2000
x
1
1/11/2000
x
1
1/12/2000
x
1
12/31/2000
x
2
1/1/2000
x
2
1/2/2000
x
2
1/3/2000
x
2
1/4/2000
x
2
1/5/2000
x
2
1/8/2000
x
2
1/9/2000
x
2
1/10/2000
x
2
1/11/2000
x
2
1/12/2000
x
;
run;

data want;
 set have;
 by cusip;
 if first.cusip then n=0;
 n+1;
 if first.cusip or mod(n,3)=1 then group+1;
 drop n;
run;
Zerg
Calcite | Level 5
Thank you. I incorporated the group variable created by your codes in my sql codes and solved the issue.

proc sql undo_policy = none;
create table abret_2005 as select distinct
cusip, group, exp(sum(log(1+madjret))) -1 as ab_ret, n(group) as nobs
from w3_2005
group by cusip, group
order by cusip, group;
quit;

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
  • 5 replies
  • 2588 views
  • 0 likes
  • 3 in conversation