Help with Missing values (.) in a Panel

[Falco Wolf]

Hello all!

've ran into a problem that I do not know how to solve. I have a Dataset that I will need to manipulate in order to create a easier to read Panel Data. I have four variables, id (which gives me the ID company number in the data set) companyname (the name of the company) status (categorical with 16 different activity types) and a few more that are nonimportant for now.

(Look below to see sample)
As you can see, I have an id and company name which is related to Status. It's the history of the company considering it's activity status. The ID and Companyname do not progress until the whole company status history is printed.

My problem is, I need to make every missing id "." (dot) convert to it's company id and fill company name "blank" with it's corresponding for id as well.

Example (look at table for clarity): obs 173 has id 112 with companyname: EIM (FX) LIMITED status dissolved obs 174 has id . with companyname: status In liquidiation . . .
And I need it to be like this:

How it should be: obs 173 should have id 112 with companyname: EIM (FX) LIMITED status dissolved obs 174 has id 112 with companyname: EIM (FX) LIMITED status In liquidation.And so on, for all the data set.

How can I do a command to do it automatically? I was thinking maybe with a loop?

. list	id companyname status in 173/191
id companyname	status
173.	112 EIM (FX) LIMITED	Dissolved
174.	.	In liquidation
175.	.	Active
176.	.	Active (dormant)
177.	.	Active
178.	113 E.ON VERTRIEB DEUTSCHLAND GMBH	Dissolved (merger or take-over)
179.	114 PRUDENTIAL ASSURANCE COMPANY LIMITED	Dissolved
180.	115 CHEVRON LIMITED	Dissolved
181.	116 KOREA TELECOM	Dissolved
182.	117 TXU EUROPE GROUP PLC	In liquidation
183.	.	Active (insolvency proceedings)
184.	118 TELSTRA CORPORATION LIMITED	Dissolved
185.	119 NORTH SEA GROUP B.V.	Dissolved
186.	.	Active
187.	120 VODAFONE GMBH	Dissolved
188.	121 STINNES AG	Dissolved
189.	122 VEDIOR NV	Dissolved
190.	.	Active
191.	123 ZURICH LEBENSVERSICHERUNG AKTIENGESELLSCHAFT (DEUTSCHLAND)	Dissolved (merger or take-over)

Any help would be greatly appreciated. Thank you in advance.

[Clyde Schechter]

Your table showing the data is a bit mangled. I will assume that id and companyname are two separate variable in your data set.

In that case, after -use-ing the data set, and before you do anything else, run:

replace id = id[_n-1] if missing(id)
replace companyname = companyname[_n-1] if missing(companyname)

Note: not tested. Beware of typos.

In the future, when showing data examples, please use the -dataex- command to do so. If you are running version 15.1 or a fully updated version 14.2, it 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-.

[Falco Wolf]

Clyde,

Thank you very much. To copy tables from stata to here, it's better to just copy - paste the table produced with the dataex? I will probably have more questions in the future.

Thank you.

[Falco Wolf]

I've ran to a few obstacles that should be easy to do.
1) I need to eliminate the observations of the first colum and move all the column observations one column to the left
The observations in the 2nd column should be in column 1 (company name). The Observations in column 3 should be in column 2(nacerev2corecode4digits). And so on until the last column.
Is there a way to do this by using commands? Or maybe it's just easier eliminating the first column and re-namingthe variables?

2) This one is the one I cannot really get to do. This second column has the identification number of the country (two first letters of the ID) and then the number of the sector. I am only interested in the country. How can I tell Stata to read the first two first letters of every observation in the column, and if it's IT to change it for Italy, if its BE for Belgium, if its NL Neatherlands (and so on. As long as I understand how to do it for one, I can extrapolate to the rest). Perhaps instead of substituting I could create a new variable called country and put the observation there.

3) In the Statusdate variable, is there a way so that I can only put the year isntead of month day and year?

This is all. The most important is the second as the other two I believe I can manage by trial and error. But the second one I'm pretty lost.

Here is the dataex:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long companyname str254 nacerev2corecode4digits str4 bvdidnumber str18 status str31 statusdate str10(statusupdateddate v7)
  1 "KONINKLIJKE/SHELL GROEP"                                                "600"  "NL99111033"    "Inactive (no precision)"         ""           ""          
  2 "EXOR SPA"                                                               "6420" "IT00470400011" "Dissolved"                       "12/29/2016" "01/07/2017"
  3 "GIOVANNI AGNELLI E C. SOCIETA' IN ACCOMANDITA PER AZIONI"               "6492" "IT04735350011" "Dissolved"                       "01/13/2017" "01/19/2017"
  4 "ENGIE SA"                                                               "3522" "BE0472018331"  "Inactive (no precision)"         ""           "05/02/2014"
  5 "ENGIE SA"                                                               "3522" "BE0472018331"  "Active"                          "06/09/2000" ""          
  6 "FIAT S.P.A."                                                            "7010" "IT00469580013" "Dissolved (merger or take-over)" "10/31/2014" "12/05/2014"
  7 "FIAT S.P.A."                                                            "7010" "IT00469580013" "Active"                          ""           ""          
  8 "SUNSHINE FINANCE COMPANY B.V."                                          "6420" "NL34280119"    "Dissolved"                       "10/16/2014" "10/16/2014"
  9 "SUNSHINE FINANCE COMPANY B.V."                                          "6420" "NL34280119"    "In liquidation"                  "05/02/2014" "05/02/2014"
 10 "SUNSHINE FINANCE COMPANY B.V."                                          "6420" "NL34280119"    "Active"                          "08/08/2007" ""          
 11 "NOKIA NEDERLAND B.V."                                                   "4652" "NL27156399"    "Dissolved"                       "01/17/2013" "01/17/2013"
 12 "NOKIA NEDERLAND B.V."                                                   "4652" "NL27156399"    "Active"                          "12/18/1991" ""          
 13 "SUEZ"                                                                   "3511" "FR542062559"   "Dissolved"                       ""           "06/28/2018"
 14 "MITTAL STEEL COMPANY N.V."                                              "2452" "NL24275428"    "Dissolved"                       "09/21/2007" "02/29/2012"
 15 "MITTAL STEEL COMPANY N.V."                                              "2452" "NL24275428"    "Active"                          "05/27/1997" ""          
 16 "UNITED TECHNOLOGIES CORPORATION LIMITED"                                "8299" "GB04906380"    "Dissolved"                       "09/15/2009" ""          
 17 "NSU L2 BETEILIGUNGS-GMBH"                                               "6420" "DE7070194020"  "Dissolved"                       "01/15/2008" "10/27/2015"
 18 "LLOYD'S of LONDON"                                                      "6511" "GB03189123"    "Active (dormant)"                ""           ""          
 19 "US AIRWAYS INC"                                                         "6910" "IE906356"      "Dissolved"                       ""           "04/19/2016"
 20 "US AIRWAYS INC"                                                         "6910" "IE906356"      "Active"                          ""           ""          
 21 "TYCO ELECTRONICS CORPORATION"                                           "4619" "IE903871"      "Dissolved"                       ""           "04/22/2015"
 22 "TYCO ELECTRONICS CORPORATION"                                           "4619" "IE903871"      "Active"                          ""           ""          
 23 "FIAT INDUSTRIAL S.P.A."                                                 "7010" "IT10352520018" "Dissolved (merger or take-over)" "10/30/2013" "11/19/2013"
 24 "FIAT INDUSTRIAL S.P.A."                                                 "7010" "IT10352520018" "Active"                          ""           ""          
 25 "MARATHON POWER IRELAND LIMITED"                                         "4690" "IE904085"      "Dissolved"                       ""           "04/22/2015"
 26 "MARATHON POWER IRELAND LIMITED"                                         "4690" "IE904085"      "Active"                          ""           ""          
 27 "TELECOM ITALIA S.P.A. (OLD)"                                            "6190" "IT00471850016" "Dissolved (merger or take-over)" ""           "04/19/2012"
 28 "TELECOM ITALIA S.P.A. (OLD)"                                            "6190" "IT00471850016" "Dissolved"                       ""           ""          
 29 "MARATHON INTERNATIONAL PETROLEUM IRELAND LIMITED"                       "610"  "IE903713"      "Dissolved"                       ""           "04/22/2015"
 30 "MARATHON INTERNATIONAL PETROLEUM IRELAND LIMITED"                       "610"  "IE903713"      "Active"                          ""           ""          
 31 "FIAT NETHERLANDS HOLDING N.V."                                          "6420" "NL33142210"    "Dissolved"                       "08/30/2013" "08/30/2013"
 32 "FIAT NETHERLANDS HOLDING N.V."                                          "6420" "NL33142210"    "Active"                          "01/09/1975" ""          
 33 "SK ENERGY INTERNATIONAL PTE LTD"                                        "910"  "IE908381"      "Dissolved"                       ""           "10/31/2017"
 34 "SK ENERGY INTERNATIONAL PTE LTD"                                        "910"  "IE908381"      "Active"                          ""           "03/01/2016"
 35 "AGIP PETROLI SPA"                                                       "610"  "IT02929200588" "Inactive (no precision)"         ""           "05/04/2016"
 36 "AGIP PETROLI SPA"                                                       "610"  "IT02929200588" "Active"                          ""           ""          
 37 "HESS EXPLORATION IRELAND LIMITED"                                       "7111" "IE905610"      "Dissolved"                       ""           "08/27/2014"
 38 "HESS EXPLORATION IRELAND LIMITED"                                       "7111" "IE905610"      "Active"                          ""           ""          
 39 "ENBW TRADING GMBH"                                                      "3511" "DE7110179813"  "Dissolved (merger or take-over)" "04/30/2014" "10/27/2015"
 40 "ARCANDOR AG"                                                            "4719" "DE5110000390"  "Active (insolvency proceedings)" "09/01/2009" ""          
 41 "KARSTADT GMBH"                                                          "4690" "DE5110127327"  "Active (insolvency proceedings)" "09/01/2009" ""          
 42 "KARSTADT QUELLE BUSINESS SERVICES GMBH"                                 "8299" "DE5110231950"  "Active (insolvency proceedings)" "09/01/2009" ""          
 43 "STET-SOCIETA'FINANZIARIA TELEFONICA PER AZIONI"                         "6190" "IT90012EI"     "Inactive (no precision)"         ""           "05/04/2016"
 44 "STET-SOCIETA'FINANZIARIA TELEFONICA PER AZIONI"                         "6190" "IT90012EI"     "Active"                          ""           ""          
 45 "ING VERZEKERINGEN NV"                                                   "6500" "NL33260659"    "Dissolved (merger or take-over)" ""           "01/16/2016"
 46 "E.ON RUHRGAS AG"                                                        "3521" "DE5110013057"  "Dissolved (merger or take-over)" "05/07/2013" "05/05/2017"
 47 "ROBERT BOSCH INDUSTRIE- ANLAGEN GESELLSCHAFT MIT BESCHRAENKTER HAFTUNG" "6832" "DE7330008642"  "Dissolved (merger or take-over)" ""           "09/21/2016"
 48 "ROBERT BOSCH INDUSTRIE- ANLAGEN GESELLSCHAFT MIT BESCHRAENKTER HAFTUNG" "6832" "DE7330008642"  "Active"                          ""           ""          
 49 "THE COCA-COLA EXPORT CORPORATION"                                       "8299" "IE901408"      "Dissolved"                       ""           "08/06/2014"
 50 "THE COCA-COLA EXPORT CORPORATION"                                       "8299" "IE901408"      "Active"                          ""           ""          
 51 "AVENTIS SA"                                                             "2120" "FR542064308"   "Dissolved (merger or take-over)" ""           ""          
 52 "MERCK SHARP & DOHME (IRELAND) LIMITED"                                  "2110" "IE903991"      "Dissolved"                       ""           "04/22/2015"
 53 "MERCK SHARP & DOHME (IRELAND) LIMITED"                                  "2110" "IE903991"      "Active"                          ""           ""          
 54 "BHP BILLITON WORLD EXPLORATION INC."                                    "7219" "IE904008"      "Dissolved"                       ""           "05/11/2016"
 55 "BHP BILLITON WORLD EXPLORATION INC."                                    "7219" "IE904008"      "Active"                          ""           ""          
 56 "ZF SACHS AG"                                                            "2932" "DE8310000345"  "Dissolved"                       ""           ""          
 57 "MOCATTA & GOLDSMID LIMITED"                                             "8299" "GB00582757"    "Dissolved"                       "06/29/2004" ""          
 58 "ASM HOLDING GMBH"                                                       "6420" "DE4130036245"  "Dissolved (merger or take-over)" ""           ""          
 59 "BERTELSMANN MULTIMEDIA GMBH"                                            "6311" "DE4130052756"  "Dissolved (merger or take-over)" ""           ""          
 60 "BERTELSMANN IMMOBILIEN GMBH"                                            "6832" "DE4130060969"  "Dissolved (merger or take-over)" ""           ""          
 61 "WITTINGTON INVESTMENTS LIMITED"                                         "1089" "GB00366054"    "Active (dormant)"                ""           "10/25/2017"
 62 "WITTINGTON INVESTMENTS LIMITED"                                         "1089" "GB00366054"    "Active"                          ""           "07/07/2016"
 63 "ALFRED C. TOEPFER INTERNATIONAL B.V."                                   "4611" "NL24097194"    "Dissolved (merger or take-over)" "12/30/2014" "10/27/2015"
 64 "VIAG AG"                                                                "2442" "DE8170006903"  "Dissolved (merger or take-over)" ""           ""          
 65 "GRUPO FERROVIAL S.A."                                                   "4211" "ESA28606556"   "Dissolved"                       "12/15/2009" "04/11/2014"
 66 "AG2R"                                                                   "4211" "ESA28606556"   ""                                ""           ""          
 67 "AG2R"                                                                   "4211" "ESA28606556"   ""                                ""           ""          
 68 "AG2R"                                                                   "4211" "ESA28606556"   ""                                ""           ""          
 69 "CSF FRANCE"                                                             "4711" "FR501238414"   "Dissolved"                       "05/05/2014" "08/13/2014"
 70 "CSF FRANCE"                                                             "4711" "FR501238414"   "Active"                          ""           ""          
 71 "TELEFONICA MOVILES, S.A."                                               "6190" "ESA82573759"   "Dissolved"                       "08/09/2006" "04/11/2014"
 72 "CNH GLOBAL N.V."                                                        "2830" "NL33283760"    "Dissolved"                       "10/10/2013" "10/10/2013"
 73 "CNH GLOBAL N.V."                                                        "2830" "NL33283760"    "Active"                          "08/30/1996" ""          
 74 "COMPAGNIE NATIONALE A PORTEFEUILLE S.A."                                "3511" "BE0404676971"  "In liquidation"                  "10/03/2011" "02/08/2013"
 75 "COMPAGNIE NATIONALE A PORTEFEUILLE S.A."                                "3511" "BE0404676971"  "Dissolved (merger or take-over)" ""           ""          
 76 "GESPARAL"                                                               "6420" "FR692045461"   "Dissolved"                       "06/08/2004" ""          
 77 "ASKO DEUTSCHE KAUFHAUS AG"                                              "4711" "DE02393ED"     "Inactive (no precision)"         ""           ""          
 78 "TOTAL HOLDINGS DEUTSCHLAND GMBH"                                        "7010" "DE2010608683"  "Dissolved (merger or take-over)" ""           ""          
 79 "TELECOM ITALIA MOBILE S.P.A."                                           "6190" "IT06947890015" "Dissolved (merger or take-over)" ""           "03/28/2012"
 80 "TELECOM ITALIA MOBILE S.P.A."                                           "6190" "IT06947890015" "Dissolved"                       ""           ""          
 81 "VATTENFALL EUROPE AKTIENGESELLSCHAFT"                                   "7010" "DE2011425555"  "Dissolved (merger or take-over)" "09/17/2012" "10/27/2015"
 82 "SOCIETE FRANCAISE DU RADIOTELEPHONE-SFR"                                "7010" "DE2011425555"  ""                                ""           ""          
 83 "SOCIETE FRANCAISE DU RADIOTELEPHONE-SFR"                                "7010" "DE2011425555"  ""                                ""           ""          
 84 "IBM INTERNATIONAL HOLDINGS BV"                                          "6420" "NL33287719"    "Dissolved"                       "01/20/2011" "02/29/2012"
 85 "IBM INTERNATIONAL HOLDINGS BV"                                          "6420" "NL33287719"    "Active"                          "12/30/1996" ""          
 86 "GEELY SWEDEN AB"                                                        "7010" "SE5567989966"  "Dissolved (merger or take-over)" ""           "02/20/2017"
 87 "GEELY SWEDEN AB"                                                        "7010" "SE5567989966"  "Dissolved"                       ""           "01/11/2016"
 88 "GEELY SWEDEN AB"                                                        "7010" "SE5567989966"  "Dissolved (merger or take-over)" ""           "08/10/2015"
 89 "GEELY SWEDEN AB"                                                        "7010" "SE5567989966"  "Active"                          ""           ""          
 90 "OW BUNKER A/S"                                                          "4778" "DK34900167"    "Bankruptcy"                      ""           "11/28/2014"
 91 "OW BUNKER A/S"                                                          "4778" "DK34900167"    "Active"                          ""           "09/26/2014"
 92 "O.W. BUNKER & TRADING A/S"                                              "4671" "DK66441717"    "Bankruptcy"                      "11/12/2014" "10/24/2017"
 93 "O.W. BUNKER & TRADING A/S"                                              "4671" "DK66441717"    "Inactive (no precision)"         "11/12/2014" "11/24/2014"
 94 "O.W. BUNKER & TRADING A/S"                                              "4671" "DK66441717"    "Active"                          ""           ""          
 95 "NIDERA HOLDINGS B.V."                                                   "6420" "NL24353180"    "Dissolved"                       "08/02/2012" "08/02/2012"
 96 "NIDERA HOLDINGS B.V."                                                   "6420" "NL24353180"    "Active"                          "09/29/2003" ""          
 97 "THYSSEN AG"                                                             "2452" "DE5070003565"  "Dissolved (merger or take-over)" ""           ""          
 98 "HOCHTIEF"                                                               "4399" "BE0405942030"  "Inactive (no precision)"         "12/31/2012" "04/09/2013"
 99 "HOCHTIEF"                                                               "4399" "BE0405942030"  "Active"                          ""           ""          
100 "RWE ENERGY AKTIENGESELLSCHAFT"                                          "7010" "DE4070219640"  "Dissolved"                       ""           ""          
end

[David Benson]

To extract the first 2 letters out of variable bvdidnumber:

gen country_code = substr(bvdidnumber, 1, 2)

Then you can manually create country variables based on the two-letter country_code (i.e. gen country = "Italy" if country_code=="IT"), and so on. If there are lots of countries, then you can create a Stata file that has a master list of countries and their two-letter country_codes, and then just merge them in to this file matching on country_code. If you are going to be doing this a lot (or using this data source a lot), this is the way to go.


To extract the year from statusupdateddate, since it is a string (and the year is always the last 4-digits), you could do something similar:

gen year_string = substr(statusupdateddate, -4, .)
destring year_string, gen(year)  // substr() will create a new var that is a string. This converts it to a number
drop year_string  // probably no need to keep it