How to create a dummy within a categorical variable.

[Aamir Khan]

Hello everyone,
i am working on Mergers and Acquisition. I have a variable named "Acquirer Name" which are the names of the acquirer companies. Some acquirers names appears more than one time in the list. I need to create two dummy variables named "First Time Acquirers" and "Serial Acquirers". The acquirers will be code 1 under serial acquirers which have more than one acquisitions and 0 for other wise. Same for the First time acquires the dummy will be coded 1 for first time acquirers and 0 for otherwise. Thanks

[Clyde Schechter]

Your question is confusing, in part because you do not provide any example data to enable people to see how your data is organized. (I can imagine more than one way your data might look that are consistent with your description, but each would require a somewhat different solution.) I am also unclear why you want two variables here. It sounds to me as if the two variables you speak of are just each the opposite of the other. So only one is necessary.

Or did you mean that for acquirers who are serial acquirers, you want their first acquisition to be marked by an indicator variable? If so, you need some date variable to help identify which one is first.

Please clarify the question, and post back using the -dataex- command to show a brief example of your data. If you are running version 15.1 or a fully updated version 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Aamir Khan]

Thank for the information. I will post it again with data example.

[David Benson]

Hi Aamir, see if this works. Like Clyde, I'm not sure if "First Time Acquirers" meant:
(a) they only made one acq during entire sample period or
(b) It was the company's first acq (but they could have gone on to make more during the sample period).

I've given code for both below:

* Note: Some of these acq are real, but I made up the dates, so consider this toy data
dataex acq_id acq_name acq_date target_name  // data shared via -dataex-. To install: ssc install dataex
clear
input byte acq_id str8 acq_name int acq_date str19 target_name
1 "IBM"      16742 "Tivoli Systems Inc"
2 "Cisco"    16747 "Nashoba Networks"  
2 "Cisco"    16797 "Granite Systems Inc"
2 "Cisco"    16840 "NETSYS Technologies"
2 "Cisco"    16884 "Ardent Comms"      
2 "Cisco"    16949 "LightSpeed Intl"    
3 "EMC CORP" 15608 "Prisa Networks Inc"
3 "EMC CORP" 15810 "Astrum Software"    
3 "EMC CORP" 16054 "VMware Inc"        
4 "Dropbox"  16632 "Mainspring Inc"    
end
format %tdD-m-Y acq_date

bysort acq_id (acq_date): gen n = _n
bysort acq_id (acq_date): gen total_acq = _N
bysort acq_id (acq_date): gen serial_acq = ( total_acq>=2)
bysort acq_id (acq_date): gen first_time = (n==1)  // this is 1st acq (in time period), but acquirer may have more
bysort acq_id (acq_date): gen only_one = (total_acq==1)  // acquirer made only 1 acq over entire sample time period

. list, sepby(acq_id) noobs abbrev(12)

  +----------------------------------------------------------------------------------------------------------+
  | acq_id   acq_name    acq_date           target_name   n   total_acq   serial_acq   first_time   only_one |
  |----------------------------------------------------------------------------------------------------------|
  |      1        IBM   02-Nov-05    Tivoli Systems Inc   1           1            0            1          1 |
  |----------------------------------------------------------------------------------------------------------|
  |      2      Cisco   07-Nov-05      Nashoba Networks   1           5            1            1          0 |
  |      2      Cisco   27-Dec-05   Granite Systems Inc   2           5            1            0          0 |
  |      2      Cisco   08-Feb-06   NETSYS Technologies   3           5            1            0          0 |
  |      2      Cisco   24-Mar-06          Ardent Comms   4           5            1            0          0 |
  |      2      Cisco   28-May-06       LightSpeed Intl   5           5            1            0          0 |
  |----------------------------------------------------------------------------------------------------------|
  |      3   EMC CORP   25-Sep-02    Prisa Networks Inc   1           3            1            1          0 |
  |      3   EMC CORP   15-Apr-03       Astrum Software   2           3            1            0          0 |
  |      3   EMC CORP   15-Dec-03            VMware Inc   3           3            1            0          0 |
  |----------------------------------------------------------------------------------------------------------|
  |      4    Dropbox   15-Jul-05        Mainspring Inc   1           1            0            1          1 |
  +----------------------------------------------------------------------------------------------------------+

[Aamir Khan]

Daer David, Thanks for the codes you provided. Its work quite well. Dear Clyde, Thanks for you guidance.

[Aamir Khan]

Dear Clyde, as you mentioned "I am also unclear why you want two variables here. It sounds to me as if the two variables you speak of are just each the opposite of the other. So only one is necessary". I read one thesis/published work in which they used two dummy variables, one for serial_acquirers_dummy which is coded 1 and for companies with more than one acquisition and 0 for otherwise. another First_time_acquirers_dummy coded 1 for firms with only one acquisitions and 0 for otherwise. I am also confused about this that is it a good practice. Is it more better to use only one dummy coded 1 for serial acquirers and 0 for first time acquirers. In the same way they used two dummies for industry relatedness 1) Horizontal acquisitions coded one for target and acquirers having same industry and 0 for otherwise. 2) Vertical acquisitions coded 1 for target and acquirers having efferent industries and 0 for otherwise. In this case too, I think one dummy will be more better coded 1 for horizontal acquisitions and 0 for vertical acquisitions. Need yo kind opinion please. Thanks

[Clyde Schechter]

Yes, if you have two variables where one of them is the opposite of the other, it makes no sense to have both: pick one and get rid of the other.

[Aamir Khan]

Ok, thanks for guidance