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