Solved
Contributor
Posts: 36

Add index number around an event date

I am doing an event study research project. I need to calculate the total abnormal return (abn_d) around each event. Since I need to try different time window, I would like to add a index date relative the event date. If  date = rating_date, relday=0; if the date is the earlier date , relday =-1, or -2, -3.....; if the date is after the rating_date reladay = +1, +2, +3...... But my actual date is not continuous, there are missing dates, so I cannot use date+1 , +2 +3...or date-1, -2, -3.. to reach the goal.

II attached the file. My goal is to create a new variable "relday" to show the date relative to rating_date, then I can choose the time window ( e.g. (-10,+5) ) and calculate the sum of abn_d over this time window. Again, the attached file is a simpler version of my actual data.

I really appreciate helps from the SAS experts here.

Accepted Solutions
Solution
‎02-13-2013 04:04 PM
Posts: 3,167

Re: Add index number around an event date

Not sure if this is what you want:

data want;

/*to locate the record most close to rating_date*/

do until (last.eventid);

set test.test;

by eventid date;

_dif=abs(RATING_DATE-date);

_mindif=min(_mindif,_dif);

end;

/*the get the seq no of that record*/

do _n_=1 by 1 until (last.eventid);

set test.test;

by eventid date;

if abs(RATING_DATE-date)=_mindif then _tp=_n_;

end;

/*assign the new seq no*/

do relday=-_tp by 1 until (last.eventid);

set test.test;

by eventid date;

output;

end;

drop _:;

run;

Haikuo

All Replies
SAS Employee
Posts: 417

Re: Add index number around an event date

Hello -

data want;

set have;

relday=date-rating_date;

run;

Results in:

Thanks,

Udo

Contributor
Posts: 36

Re: Add index number around an event date

Undo, thank you.

But the relday is not continuous number. I would like to see all continuous integers: -9, -10, -2, -3... are missing for relday. I need to make sure when I specify the time window (-5,+5), there are same numbers of trading day for each firm.

Anna

SAS Employee
Posts: 417

Re: Add index number around an event date

Anna -

Not sure if I fully understand what you would like to see - I will give it another try (assuming that you have access to SAS/ETS software for the time being).

I think what you are asking for is first of all how to fill gaps in your time id (date variable) and then to calculate relday, correct?

If so, running the following code:

*first fill gaps in timeid;

proc timeseries data=want out=temp;

id date interval=day accumulate=total;

var abn_d;

var rating_date / setmissing=prev;

by eventid;

run;

*then calculate relday;

data want;

set temp;

relday=date-rating_date;

run;

results in:

If this is not what you have in mind a illustration of WANT would be useful.

Thanks,
Udo (aka Undo)

Contributor
Posts: 36

Re: Add index number around an event date

Udo,

Thank you for your help.  For the illustration  table you provided, for the date of March 15, 1996, I would like to see the relday=-9 ( not -11),  and the dates when abn_d missing, I would like to see relday also missing.

Anyone can give me more help?

Thank you!

Anna

Contributor
Posts: 36

Re: Add index number around an event date

Anna

Solution
‎02-13-2013 04:04 PM
Posts: 3,167

Re: Add index number around an event date

Not sure if this is what you want:

data want;

/*to locate the record most close to rating_date*/

do until (last.eventid);

set test.test;

by eventid date;

_dif=abs(RATING_DATE-date);

_mindif=min(_mindif,_dif);

end;

/*the get the seq no of that record*/

do _n_=1 by 1 until (last.eventid);

set test.test;

by eventid date;

if abs(RATING_DATE-date)=_mindif then _tp=_n_;

end;

/*assign the new seq no*/

do relday=-_tp by 1 until (last.eventid);

set test.test;

by eventid date;

output;

end;

drop _:;

run;

Haikuo

Contributor
Posts: 36

Re: Add index number around an event date

Hai.Kuo

Thank you very much!!!

This is exactly what I want. I did the other event study last year and I used excel (vlookup) to add the relative trading date index to my data. It was painful. I wished I had joined SAS communities earlier.