Assigning rank to a variable based on 2 other variables in stata

Question

I have a longitudinal data with some variables as: firm id, event date, abnormal return, where event date is acquisition announcement date by each firm and abnormal return is the event-announcement return for each firm over a window of 5 days (-2,+2). Here each sample firm engages in multiple acquisitions over a pre-defined period.

For e.g., I have 200 firms, each having multiple abnormal return-observations for the period 1999-2011. Therefore, each firm has at least 2 such observations during this period, computed over 2 such event dates.

I need to assign a rank to the abnormal returns for each firm so as to rank the subsequent deals (after the first acquisition by a firm) in order of its event dates. So, I want to create a new rank variable and use it for further analysis of a firm’s increase/decrease in each subsequent abnormal return after the first one.

Please suggest how can I perform this in Stata or what codes to use? I tried with:

bysort firm_id event_date: egen rank = rank(abnormal_return), 

but I’m not getting what I want.

N.B. – a) In my sample, there are few firms which perform more than 1 deal (say, 2) on the same event date, so they have exactly same abnormal returns for those 2 deals; b) Abnormal returns are either positive or negative and in 6/7 places of decimal, for e.g. a negative abnormal return looks like, -0.0365089; a positive one, 0.0416888.

I'm sorry I've already posted this query in statalist, which I ought to have mentioned in the post, but somehow in a hurry to get feedback, I missed it - sincere apologies.

(UPDATE) I've finally managed to share the dropbox link of a snapshot of my dta. file.

https://www.dropbox.com/s/w501upimdgwvzyz/Rank.dta?dl=0

The query i have now (which hasn't been answered at statalist yet & i'm trying to do an analysis on the basis of this, hence re-posting here):

If I am looking at a window of 3 years, i.e., I need to re-rank the abnormal returns on the basis that the subsequent deals had been made in a span of 3 years from the first deal. For e.g., a firm in my dta. file with id = 13 has 4 deals in years 2000, 2001, 2005 and 2006, respectively. The first deal is in year 2000. The 3rd acquisition in year 2005, which is more than 3 years from the focal deal in year 2000, hence shouldn’t be counted or ranked from the 1st deal in year 2000, rather year 2005 will be the new 1st deal for this firm for another span of 3 years. So, in this case, I shall have to rank the deals by firm_id, 13 as 0 (2000), 1(2001); 0 (2005), 1 (2006).

Can anybody please help with the re-coding here?

Answer 1

In comments you noted that what you want is given by

sort firm_id event_date 
by firm_id : gen rank = _n 
bysort firm_id event_date : replace rank = rank[1] 

That can be condensed to

bysort firm_id (event_date): gen rank = _n 
by firm_id event_date: replace rank = rank[1]