Hello!
I need some help creating a FOR-loop to subset my data.
Dataset:
330 000 successful & unsuccesful acquisition bids in the US, dates 01/01/80 - 31/12/20 (see screenshot).
Variables:
- Bid announcement date (ANDATE)
- Bid completion date (EFFDATE) - date if bid is successful. NA if unsuccessful
- Bid ID (ID)
- Target ID (T_CUSIP)
Subset criteria
I want to keep the cases where there is competition for the target. This is defined as follows:
-
2 bids are announced for the same target (T_CUSIP) within 12 months of each other (ANDATE)
-
Neither bid can be completed before the other is announced (EFFDATE > ANDATE)
Problem
A target might appear several times in the data, as multiple acquirers could bid for the target, and the same target can be acquired multiple times.
For example, there might be competition for a target in the year 1980 (bidding round 1), and then again in the year 2000 (bidding round 2). If we mindlessly implement the criteria that all bids for the same target must be within 12 months of each other, the second bidding round would disappear all together.
I therefore think I need a FOR-loop. My basic idea is this:
- Subset the data to include only T_CUSIPs that appear 2+ times
- Sort the dataframe by T_CUSIP so that all bids for the same target appear together ordered by ANDATE
- Create a FOR-loop where each bid is controlled against the next bid for the same target, to see if they were announced within 12 months of each other.
-If yes, check if ANNDATE of bid 2 < EFFDATE of bid 1.
-If yes again, store both successful and unsuccessful bids in new competition dataframe.
-If no, move on to the next observation.
When this is done for the case of 2 bids for the same target, I want to repeat the same exercise for cases where there are 3 or more bids for the same target. Only now, the bids can be 24 months apart instead of 12.
A look at my data:
