Generate new variable by deal Id

[Serena Gallo]

Hi everyone,
I am studying syndication in Venture Capital.
Each deal has different types of investors; such as Venture Capital, Government, etc.

I want to create a variable that takes value 1, if the investors are both Venture Capital and Government in the same deal, while 0 if the investor is only Venture Capital.
Can I do it?

[Clyde Schechter]

Without example data, I don't see how anybody can answer this question. Please use the -dataex- command for this. If you are running version 17, 16 or a fully updated version 15.1 or 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-.

[Serena Gallo]

Sorry. It is my dataset. For each deal, I want to create a variable that takes the value 1 if the PrimaryInvestor is only Venture Capital; 2 if the PrimaryInvestors are Venture Capital and Sovereign Wealth Fund...and etc.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str28 DealID str75 CompanyName str42 PrimaryInvestorType
"82335-88T"  "1Check"                                        "Venture Capital"      
"82335-88T"  "1Check"                                        "Sovereign Wealth Fund"
"82335-88T"  "1Check"                                        "Venture Capital"      
"82335-88T"  "1Check"                                        "Angel (individual)"   
"82335-88T"  "1Check"                                        "Venture Capital"      
"82335-88T"  "1Check"                                        "Secondary Buyer"      
"82335-88T"  "1Check"                                        "Government"           
"82335-88T"  "1Check"                                        "Venture Capital"      
"190689-31T" "Ad-Venta"                                      "Corporation"          
"103075-03T" "Addworking"                                    "Accelerator/Incubator"
"105731-47T" "Advalo"                                        "Accelerator/Incubator"
"108427-33T" "Aeromate"                                      "Accelerator/Incubator"
"110289-43T" "Agorize"                                       "Accelerator/Incubator"
"109119-88T" "alf"                                           "Accelerator/Incubator"
"93651-58T"  "Amyko"                                         "Accelerator/Incubator"
"123806-80T" "AntsRoute"                                     "Accelerator/Incubator"
"127677-16T" "ApiNapi"                                       "Accelerator/Incubator"
"105729-40T" "Aptiko"                                        "Accelerator/Incubator"
"140447-26T" "Aptys Pharma"                                  "Sovereign Wealth Fund"
"140447-26T" "Aptys Pharma"                                  "Government"           
"140447-26T" "Aptys Pharma"                                  "Corporation"          
"179869-42T" "ASTRAVEUS"                                     "Accelerator/Incubator"
"82019-62T"  "Atavik"                                        "Growth/Expansion"     
"126576-82T" "Atelier Parametre"                             "Accelerator/Incubator"
"126576-82T" "Atelier Parametre"                             "Accelerator/Incubator"
"208677-97T" "Axibio"                                        "Sovereign Wealth Fund"
"208677-97T" "Axibio"                                        "Venture Capital"      
"91532-80T"  "Be Sport"                                      "Accelerator/Incubator"
"123631-21T" "BeOp"                                          "Sovereign Wealth Fund"
"123631-21T" "BeOp"                                          "Angel (individual)"   
"123631-21T" "BeOp"                                          "Angel (individual)"   
"123631-21T" "BeOp"                                          "Angel (individual)"   
"93593-44T"  "Biomede"                                       "Accelerator/Incubator"
"171267-58T" "Bloom's"                                       "Real Estate"          
"100974-61T" "Bloomin"                                       "Accelerator/Incubator"
"125161-75T" "Bluepad"                                       "Accelerator/Incubator"
"127624-51T" "BPTA"                                          "Accelerator/Incubator"
"115951-42T" "Braave"                                        "Angel Group"          
"118427-95T" "Brut Media"                                    "Angel (individual)"   
"118840-15T" "Bureaux A Partager"                            "Venture Capital"      
"118840-15T" "Bureaux A Partager"                            "Asset Manager"        
"124896-25T" "Caducy"                                        "Accelerator/Incubator"
"101356-57T" "CamToy"                                        "Accelerator/Incubator"
"105733-81T" "Centreon"                                      "Accelerator/Incubator"
"106382-35T" "Charme & Traditions"                           "Venture Capital"      
"111487-33T" "Chateau.com"                                   "Angel (individual)"   
"82642-78T"  "ChouetteCopro"                                 "Accelerator/Incubator"
"98306-83T"  "CitizenFarm"                                   "Accelerator/Incubator"
"121704-76T" "Clac des Doigts"                               "Accelerator/Incubator"
"100553-59T" "Claranor"                                      "PE/Buyout"            
"94008-70T"  "Cleany"                                        "Accelerator/Incubator"
"103736-17T" "Clustaar"                                      "Venture Capital"      
"96934-15T"  "Communicotool"                                 "Accelerator/Incubator"
"101431-63T" "Comptoir de Campagne"                          "Angel Group"          
"139278-97T" "Concilio"                                      "Venture Capital"      
"105734-08T" "CoWork.io"                                     "Accelerator/Incubator"
"132987-52T" "CPK Consult"                                   "Accelerator/Incubator"
"110655-19T" "Cutwork"                                       "Accelerator/Incubator"
"102118-06T" "Cycloponics"                                   "Accelerator/Incubator"
"108432-37T" "Delaplace.pro"                                 "Accelerator/Incubator"
"125170-93T" "Devellyn"                                      "Accelerator/Incubator"
"122896-72T" "Diabeloop"                                     "Accelerator/Incubator"
"96175-36T"  "Digifood"                                      "Accelerator/Incubator"
"111680-20T" "Draw Me A Garden"                              "Accelerator/Incubator"
"153861-94T" "DynAdmic"                                      "PE/Buyout"            
"209386-36T" "eazee link"                                    "Venture Capital"      
"209386-36T" "eazee link"                                    "Venture Capital"      
"209386-36T" "eazee link"                                    "Angel (individual)"   
"209386-36T" "eazee link"                                    "Angel (individual)"   
"209386-36T" "eazee link"                                    "Angel (individual)"   
"209386-36T" "eazee link"                                    "Angel (individual)"   
"175415-32T" "Ecodrop"                                       "Government"           
"175415-32T" "Ecodrop"                                       "Sovereign Wealth Fund"
"139251-52T" "Emprunte Mon Toutou"                           "Accelerator/Incubator"
"120257-38T" "Eolink"                                        "Venture Capital"      
"120257-38T" "Eolink"                                        "Angel Group"          
"120257-38T" "Eolink"                                        "Venture Capital"      
"81392-50T"  "EP Tender"                                     "Government"           
"109085-14T" "Epopia"                                        "Accelerator/Incubator"
"95421-97T"  "Ermeo"                                         "Accelerator/Incubator"
"101372-32T" "Fidzup"                                        "Accelerator/Incubator"
"122403-16T" "Fintecture"                                    "Accelerator/Incubator"
"75946-96T"  "Fx Solutions (France)"                         "PE/Buyout"            
"75946-96T"  "Fx Solutions (France)"                         "Venture Capital"      
"75946-96T"  "Fx Solutions (France)"                         "Venture Capital"      
"75946-96T"  "Fx Solutions (France)"                         "PE/Buyout"            
"136792-09T" "Gesadour"                                      "Venture Capital"      
"88081-57T"  "GoBeep (Media and Information Services (B2B))" "Accelerator/Incubator"
"135347-50T" "Groupe Chadasaygas Méthajoule"                "Venture Capital"      
"128912-77T" "HAïKU"                                        "Accelerator/Incubator"
"103078-09T" "Hello Garage"                                  "Accelerator/Incubator"
"92347-57T"  "Hopstay"                                       "Accelerator/Incubator"
"128738-98T" "Hyperbolyk"                                    "Accelerator/Incubator"
"127155-88T" "Hyphen Pro"                                    "Accelerator/Incubator"
"109467-37T" "HySiLabs"                                      "Accelerator/Incubator"
"176814-28T" "Iktos"                                         "Venture Capital"      
"176815-63T" "Iktos"                                         "Government"           
"121294-54T" "InBrain Pharma"                                "Accelerator/Incubator"
"133177-60T" "Inheart"                                       "Accelerator/Incubator"
"118708-39T" "Innov+"                                        "Accelerator/Incubator"
end

[Clyde Schechter]

OK, but there are many deals, in fact the majority, in your example data, where there is no Venture Capital investor. What do you want to do with those cases?

[Serena Gallo]

I want to analyze the syndication phenomenon, also there is no Venture Capital investor. I want to create a variable that takes the value 1 if the type of investor is the same for each deal; the value 2 if there are two different types of investors in the same deal; the value 3 if there are three or more different types of investors in the same deal.

[Clyde Schechter]

by DealID (PrimaryInvestorType), sort: gen n_investor_types ///
    = sum(PrimaryInvestorType != PrimaryInvestorType[_n-1])
by DealID (PrimaryInvestorType): replace n_investor_types = n_investor_types[_N]

replace n_investor_types = min(n_investor_types, 3)

[Serena Gallo]

Thank you very much.
If I have a dataset like this, and I want to create a variable that takes value 1 if there is only the type of venture capital and 2 if there are venture capital and government in the same deal,
How can get it'

Example generated by -dataex-. To install: ssc install dataex
clear
input str28 DealID str75 CompanyName str42 PrimaryInvestorType
"100088-11T" "Pherecydes Pharma" "Venture Capital"
"100088-11T" "Pherecydes Pharma" "Government"
"100088-11T" "Pherecydes Pharma" "Venture Capital"
"100088-11T" "Pherecydes Pharma" "Venture Capital"
"100088-11T" "Pherecydes Pharma" "Venture Capital"
"100105-30T" "Realbest"          "Venture Capital"
"100105-30T" "Realbest"          "Venture Capital"
"100105-30T" "Realbest"          "Venture Capital"
"100130-14T" "Owkin"             "Venture Capital"
"100130-14T" "Owkin"             "Venture Capital"
"100130-14T" "Owkin"             "Venture Capital"
"100145-62T" "Allmyhomes"        "Venture Capital"
"100155-52T" "Preligens"         "Venture Capital"
"100155-88T" "Authenteq"         "Venture Capital"
"100155-88T" "Authenteq"         "Venture Capital"
"100155-88T" "Authenteq"         "Venture Capital"
"100156-69T" "AVA"               "Venture Capital"
"100156-69T" "AVA"               "Venture Capital"
"100156-69T" "AVA"               "Venture Capital"
"100156-87T" "AppScho"           "Venture Capital"
"100156-87T" "AppScho"           "Venture Capital"
"100156-87T" "AppScho"           "Venture Capital"
"100159-30T" "Endodiag"          "Venture Capital"
"100159-30T" "Endodiag"          "Venture Capital"
"100159-30T" "Endodiag"          "Venture Capital"
"100159-39T" "Endodiag"          "Government"     
"100169-38T" "VSORA"             "Venture Capital"
"100188-10T" "Peter"             "Venture Capital"
"100191-25T" "Snubes"            "Venture Capital"
"100196-83T" "Horizn Studios"    "Venture Capital"
"100196-83T" "Horizn Studios"    "Venture Capital"
"100196-83T" "Horizn Studios"    "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100231-03T" "Ledger"            "Venture Capital"
"100238-05T" "Jobandtalent"      "Venture Capital"
"100238-05T" "Jobandtalent"      "Venture Capital"
"100238-05T" "Jobandtalent"      "Venture Capital"
"100238-05T" "Jobandtalent"      "Venture Capital"
"100238-05T" "Jobandtalent"      "Venture Capital"
"100239-49T" "Elum"              "Venture Capital"
"100239-49T" "Elum"              "Venture Capital"
"100247-68T" "Mitte"             "Venture Capital"
"100247-68T" "Mitte"             "Venture Capital"
"100251-28T" "MotionTag"         "Venture Capital"
"100256-68T" "Entrafin"          "Venture Capital"
"100256-68T" "Entrafin"          "Venture Capital"
"100256-68T" "Entrafin"          "Venture Capital"
"100256-68T" "Entrafin"          "Venture Capital"
"100300-42T" "Chefclub"          "Venture Capital"
"100300-42T" "Chefclub"          "Venture Capital"
"100311-22T" "neotiv"            "Venture Capital"
"100325-17T" "ThrustMe"          "Venture Capital"
"100325-17T" "ThrustMe"          "Venture Capital"
"100359-37T" "Highsnobiety"      "Venture Capital"
"100359-37T" "Highsnobiety"      "Venture Capital"
"100359-37T" "Highsnobiety"      "Venture Capital"
"100359-37T" "Highsnobiety"      "Venture Capital"
"100374-40T" "Impact Hub Milano" "Venture Capital"
"100374-40T" "Impact Hub Milano" "Venture Capital"
"100388-26T" "Aeraccess"         "Venture Capital"
"100393-93T" "Isybot"            "Venture Capital"
"100394-38T" "Ezygain"           "Venture Capital"
"100394-38T" "Ezygain"           "Venture Capital"
"100400-59T" "Saveboost"         "Venture Capital"
"100425-97T" "Embold"            "Venture Capital"
"100425-97T" "Embold"            "Venture Capital"
"100443-79T" "Alzprotect"        "Venture Capital"
"100460-53T" "Zola Electric"     "Venture Capital"
"100460-53T" "Zola Electric"     "Venture Capital"
"100469-98T" "Brumbrum"          "Venture Capital"
"100502-92T" "Picto Access"      "Venture Capital"
"100502-92T" "Picto Access"      "Venture Capital"
"100503-64T" "Blacklane"         "Venture Capital"
"100503-64T" "Blacklane"         "Venture Capital"
"100504-81T" "Opinary"           "Venture Capital"
"100504-81T" "Opinary"           "Venture Capital"
"100504-81T" "Opinary"           "Venture Capital"
"100528-30T" "Datananas"         "Venture Capital"
"100528-30T" "Datananas"         "Venture Capital"
"100531-54T" "M-Sense"           "Venture Capital"
"100531-54T" "M-Sense"           "Venture Capital"
"100543-96T" "Yousign"           "Venture Capital"
"100597-33T" "Project bcause"    "Government"     
"100601-56T" "Japet"             "Venture Capital"
"100601-56T" "Japet"             "Venture Capital"
"100601-56T" "Japet"             "Venture Capital"
"100601-56T" "Japet"             "Venture Capital"
"100647-82T" "Wingly"            "Venture Capital"
"100663-84T" "Saagie"            "Venture Capital"
"100663-84T" "Saagie"            "Venture Capital"
"100663-84T" "Saagie"            "Venture Capital"
"100675-72T" "Ambiverse"         "Government"     
"100693-81T" "PKvitality"        "Government"     
end

[Clyde Schechter]

Well, again, the question is incompletely posed. There are four deals in this example where the only investor type is government. How do you want that handled?

[Serena Gallo]

For example, a variable that takes the value 1 if the only investor type is government; value 2 if the only investor type is venture capital; and value 3 if they invest together.

[Clyde Schechter]

by DealID (PrimaryInvestorType), sort: gen byte wanted = 3 ///
    if PrimaryInvestorType[1] != PrimaryInvestorType[_N]
by DealID (PrimaryInvestorType): replace wanted ///
    = cond(PrimaryInvestorType[1] == "Government", 1, 2) if wanted != 3

[Serena Gallo]

Thank you very much!