topic Re: Add index number around an event date in SAS Procedures

Add index number around an event date

Anna_Guo — Tue, 12 Feb 2013 17:34:20 GMT

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.

Re: Add index number around an event date

udo_sas — Tue, 12 Feb 2013 19:22:30 GMT

Hello -

Does this address your "relday" question?

data want;

set have;

relday=date-rating_date;

run;

Results in:

Thanks,

Udo

Re: Add index number around an event date

Anna_Guo — Tue, 12 Feb 2013 19:38:38 GMT

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

Re: Add index number around an event date

udo_sas — Tue, 12 Feb 2013 22:22:39 GMT

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)

Re: Add index number around an event date

Anna_Guo — Tue, 12 Feb 2013 22:55:46 GMT

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

Re: Add index number around an event date

Anna_Guo — Wed, 13 Feb 2013 19:54:47 GMT

Please help!!

Anna

Re: Add index number around an event date

Haikuo — Wed, 13 Feb 2013 21:04:45 GMT

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

Re: Add index number around an event date

Anna_Guo — Wed, 13 Feb 2013 22:35:41 GMT

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.

Very much appreciate your help!!