Merge more lines in 1 (by DealNumbers) preserving multiple values for the same variable

[Lucia Pierini]

Hi!
I have the following situation, please look a the picture (in the picture the file is in excel but of course it has been already imported in stata)
I would like to:
Keep just one line for deal (it is easier to keep the line where the are numbers like 3., 4., 5. in the first column) and having in the same row all data regarding the deal subtype, the deal financing and the deal method of payment. Because then this variabile will be transformed in dummy variables
Example:

DEALNUMBER DealsubtypeTO DealsubtypeRB DealsubtypeReccBidOIBR DealfinancingNBF DealfinancingCI DealmethodS DealmethodC

3. 1601045227 Public Takeover Reccomended Bid New bank facilities Capital increase Shares Cash

4. 491851 Public Takeover Reccomended Bid Ostile Intilally became recommended New bank facilities Cash

so then


3. 1601045227 1 1 0 1 1 1 1

4. 491851 1 1 1 1 0 0 1


Thanks a lot!

[Clyde Schechter]

please look a the picture (in the picture the file is in excel but of course it has been already imported in stata)

No!

You have the data in Stata. You should show it in Stata, using -dataex-. Run -ssc install dataex- to get the -dataex-, command, read the help file and follow the instructions there. That way if somebody wants to help you it's a quick copy and paste into the do-editor to recreate an exact replica of your Stata data set and work with it. What you show is too small to read, and even if it were readable, it gives nothing to work with. If you want people to help you, you need to help them help you.

[Lucia Pierini]

Thank you very much for your help, I didn't know this command.
Here it is the code generated by dataex.

While Dealtype is univocal for each deal, the Dealsubtype, Dealmethodofpayment and Dealfinancing can assume different values. And the database report them in different lines. I would like to bring these values in just one line, for example for the deal number 425519 to bring all in the row where "Dealfirstrow"=1

Once all is brought in one line to transform in dummy variables this value is simple. Or maybe is better first to transform in dummy through for example "tabulate Dealsubtype, generate(dst)" and then to bring all in one lines?

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 Dealfirstrow str10 DealNumber str68 Dealtype str36 Dealsubtype str33 Dealfinancing str16 Dealmethodofpayment
"1" "425519"     "Acquisition 100%" "Public takeover" "Capital increase - vendor placing" "Shares"
""  "425519"     "Acquisition 100%" "Recommended bid" ""                                  ""      
""  "425519"     "Acquisition 100%" ""                ""                                  ""      
"2" "422257"     "Acquisition 100%" "Recommended bid" "Capital increase - vendor placing" "Shares"
""  "422257"     "Acquisition 100%" "Public takeover" ""                                  ""      
""  "422257"     "Acquisition 100%" ""                ""                                  ""      
""  "422257"     "Acquisition 100%" ""                ""                                  ""      
""  "422257"     "Acquisition 100%" ""                ""                                  ""      
""  "422257"     "Acquisition 100%" ""                ""                                  ""      
""  "422257"     "Acquisition 100%" ""                ""                                  ""      
"3" "1601045227" "Acquisition 100%" "Public takeover" "New bank facilities"               "Shares"
""  "1601045227" "Acquisition 100%" "Recommended bid" "Capital increase - vendor placing" "Cash"  
""  "1601045227" "Acquisition 100%" ""                ""                                  ""      
""  "1601045227" "Acquisition 100%" ""                ""                                  ""      
""  "1601045227" "Acquisition 100%" ""                ""                                  ""      
end

[Phil Bromiley]

This is tricky because you have different values for the same string variables within what appears to be a single observation. One way to do this might be to reshape the data from long to wide, then rename the variables appropriately and reshape back to long.

Alternatively, you could build on

drop if Dealmethodofpayment =="" & Dealfinancing =="" & Dealsubtype ==""

sort DealNumber Dealsubtype
g dealsubtype1=""
g dealsubtype2=""
replace dealsubtype1 = Dealsubtype[_n+1] if DealNumber==DealNumber[_n+1]
replace dealsubtype2 = Dealsubtype[_n+2] if DealNumber==DealNumber[_n+2]

sort DealNumber Dealfinancing
g dealfinancing1=""
g dealfinancing2=""""
replace dealfinancing1 = Dealfinancing[_n+1] if DealNumber==DealNumber[_n+1]
replace dealfinancing2 = Dealfinancing[_n+2] if DealNumber==DealNumber[_n+2]


drop if DealNumber==DealNumber[_n-1] | DealNumber==DealNumber[_n-2]



If you have more than two additional rows, you'd just new dealsubtype3 etc. variables and more replace statements.

[Clyde Schechter]

This is a basic -reshape- problem. You just need to create the variable needed for the -j()- option.

gen long obs_no = _n    // MARK CURRENT SORT ORDER
by DealNumber (obs_no), sort: gen _j = _n
drop Dealfirstrow obs_no
reshape wide Dealtype Dealsubtype Dealfinancing Dealmethodofpayment, i(DealNumber) j(_j)

That said, I don't know where you're going with this, but bear in mind that most analyses in Stata are easier to do with the data in long layout. -reshape-ing to wide usually just makes things harder. So that's how you do it, but you may come to regret doing it.

[Lucia Pierini]

Thank you very much to both of you for the help!