st: computing days

[Stefano Bonini <sbonini@stern.nyu.edu>]

Dear users
I have the following problem:
I have a large set of events (debt ratings issued by rating agencies on debt issued by one company) for which I do have exact dates. Prior to each rating some banks have issued recommendations on the company like Buy or Sell. I do have exact dates for these as well.
Ratings are issued independently by multiple agencies. Ratings are issued on a large number of companies and each is identified by an ID. I want to calculate how many recommendations have been issued before each rating and the average number of days before the rating for all recommendations.

My dataset looks like this (for just one company ID):

RatingEvent	date	RatingDate	date120	          date90	date60
0	    14jan2002				
0	    08feb2002				
0	    20mar2002				
0	    08apr2002				
0	    08apr2002				
0	    08apr2002				
0	    09apr2002				
0	    17apr2002				
0	    30apr2002				
0	    22may2002				
0	    01jul2002				
0	    02jul2002				
0	    09jul2002				
0	    09jul2002				
0	    10jul2002				
0	    12jul2002				
1	    17jul2002	17jul2002	19mar2002	18apr2002	18may2002
0           22jul2002
1	    06aug2002	06aug2002	08apr2002	08may2002	07jun2002


The first column identifies the rating issuance. The second column is identifies the dates when recommendations AND ratings  have been issued. The third column reports only issuing dates for the rating (of course the info is identical to that reported in column 2. It's redundant). The 4th to 6th columns identify the beginning date of the 3 relevant windows: 120, 90 and 60 days, all ending, obviously on the rating issuance date.

Basically I would like to create a variable that tells me that, for instance, for the rating issued on 7/17/2002 I have 14 recommendations that have been issued on average 52 days before the rating (I have manually computed these data) if I'm looking at the 120days window. A second variable should give me the same info on the 90 days window and so on for the 60 days.
An additional problem is that the last row indicates that a few days after the first rating a second rating has been issued (possibly but not necessarily by a different agency). In such a case a number of record would be in common but some would not. Since I can identify ex-post through additional identifiers the company, the agency etc I just need to have these two infos: count and mean days of recommendations before each rating.

I've been  struggling for days. I hope you can help me.

Stefano




Stefano Bonini
Visiting Associate Professor
Department of Finance
New York University
Stern School of Business
44W 4th St. New York, NY

Ph. +1 212 998 0305
Email sbonini@stern.nyu.edu
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/