turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- SAS Programming
- /
- General Programming
- /
- Calculate abnormal returns for multiple 3-day wind...

Topic Options

- RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

03-31-2018 01:41 PM

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.

Accepted Solutions

Solution

04-02-2018
12:46 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Zerg

04-01-2018 07:13 AM

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

All Replies

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Zerg

03-31-2018 03:27 PM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to art297

03-31-2018 04:06 PM

Thank you. It looks like your example codes could do the trick after some modification. I will take a try.

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Zerg

03-31-2018 04:10 PM

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

Solution

04-02-2018
12:46 PM

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Zerg

04-01-2018 07:13 AM

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

- Mark as New
- Bookmark
- Subscribe
- RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Posted in reply to Ksharp

04-02-2018 12:48 PM

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;

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;