Problem with restructuring data set and generating new variables to do so

[Gianni Spolver]

Dear statalist,

I've experieced quite a challenge regarding the restructuring of my dataset and generating new variables. In this dataset there is each time a company (target) that is acquired by another company (acquiror), which was previously owned by a third company (Seller). Most of the acquired companies are then sold again by the initial acquirer to another company. Basically I have a dataset that looks as follows

TargetID	AcquirorID	SellerID	Dealdate	DealID
UK1	BE2	US3	21/06/1994	1
UK1	IE4	BE2	23/07/1997	2
BE5	US3	SL1	02/01/2002	3
BE5	IT4	US3	03/04/2004	4
BE5	UK8	IT4	05/06/2007	5
PO4	GI6	PO4	03/06/2009	6
PO4	US9	GI6	08/06/2010	7

I, however, want to restructure my dataset such that each observation specifies when a company acquires and sells a specific target, such that the dataset would look as follows:

AcquirorID	TargetID	Entrydate	Exitdate	EntrydealID	ExitdealID
BE2	UK1	21/06/1994	23/07/1997	1	2
IE4	UK1	23/07/1997	N.A.	2	N.A.
US3	BE5	02/01/2002	03/04/2004	3	4
IT4	BE5	03/04/2004	05/06/2007	4	5
UK8	BE5	05/06/2007	N.A.	5	N.A.
GI6	PO4	03/06/2009	08/06/2010	6	7
IS9	PO4	08/06/2010	N.A.	7	N.A.

The entrydate would thus be the date where a company acquires a specific company, whereas the exit date would be equal to the Dealdate when the acquirer becomes the seller. A similar procedure would than be used to create the entrydeal ID and Exit dealID by using the DealID. The "N.A." would imply that the company has not yet been sold by the current owner.

I have only recently started using stata and know how to generate new variables based on existing ones. I generated the entry date and exit date ID by simply using the code below, which worked fine.

//code Entrydate//
gen Entrydate = Dealdate
format %tdnn/dd/CCYY Entrydate
recast int Entrydate
//code EntrydealID//
gen Entryid = DealID

However, I can not find how to operationalize the code for the ExitDate and ExitID. Could anyone help?

Thanks in advance.

Regards,

Gianni

[Mike Lacy]

A couple of things:
1) First, I'd encourage you to re-post your example data using the -dataex- command per the FAQ. This makes it easy for someone to work with your example data, which greatly increases your chances of getting an answer.

2) I can't follow your description, starting at "I, however ... ." For example, I don't understand how "Dealdate" is *not* the date when a company acquires another company. My suggestion would be to show your description to a colleague with the hopes of getting help in more clearly specifying what you want. Breaking your question into pieces might help.

[Gianni Spolver]

Thank you, Mike for the constructive feedback and I apologize for the vague formulation. Taking into account you comments, I have tried to reformulate and illustrate my question more clearly:

In this dataset each observation is a company (TagetID) that is acquired by another company (AcquirorID), which was previously owned by a third company (SellerID). Most of the acquired companies are then sold again by the initial acquirer to another company. Basically I have a dataset that looks as follows

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3(TargetID AcquirorID SellerID) int Dealdate
"UK1" "BE2" "US3" 12590
"UK1" "IE4" "BE2" 13718
"BE5" "US3" "SL1" 15342
"BE5" "IT4" "US3" 16164
"BE5" "UK8" "IT4" 17322
"PO4" "GI6" "PO4" 18051
"PO4" "US9" "GI6" 18421
end
format %tdnn/dd/CCYY Dealdate

I want to generate two new variables based on the Dealdate. The first variable, identifies when a certain company buys a specific target (Entrydate). The second variable specifies when that company sells that specific target again (Exitdate) to another company. To give an example based on the sample above: On 6/21/1994 company BE2 buys company UK1. 06/21/1994 would than be the entry date for company BE2 in UK1. On 7/23/1997 company BE2 sells UK1 to IE4. The Exitdate for BE2 in UK1 would than be 7/23/1997.

The eventual result I would want to have for this dataex example would than be:

AcquirorID	TargetID	Entrydate	Exitdate
BE2	UK1	21/06/1994	23/07/1997
IE4	UK1	23/07/1997	N.A.
US3	BE5	02/01/2002	03/04/2004
IT4	BE5	03/04/2004	05/06/2007
UK8	BE5	05/06/2007	N.A.
GI6	PO4	03/06/2009	08/06/2010
IS9	PO4	08/06/2010	N.A.

The "N.A." would imply that the company has not yet been sold by the current owner.

I have only recently started using stata and know how to generate new variables based on existing ones. I generated the entry date by simply using the code below, which worked fine.

gen Entrydate = Dealdate
format %tdnn/dd/CCYY Entrydate
recast int Entrydate

However, I can not find how to operationalize the code for the ExitDate. Could anyone help?

[Gianni Spolver]

Update: in the meanwhile I have found the solution myself, using this code.

gen TargetID_new = ""
gen AcquirorID_new = ""
gen Entrydate = Dealdate
gen Exitdate = .
format Entrydate %td
format Exitdate %td

replace TargetID_new = TargetID
replace AcquirorID_new = AcquirorID

forvalues i = 1(1)20{
    replace Exitdate = Dealdate[_n+1] if TargetID == TargetID[_n+1]
}