Identifying company without primary issue

[Marco Errico]

Dear all,

I have a dataset with a string variable (iid) which, in case equal to "01W", it should identify whether the stock is primary stock of a company. Indeed in my dataset I have
gvkey: unique identifier of the company
isin: unique identifier of the stock.
iid: it identifies primary, secondary etc.. stocks of company.

I.e. for same gvkey, I can have different isin.

What I want to identify is whether in this dataset there is some gvkey with secondary, third etc.. stockS(e.g. 02W 03W..) but without "01W" (primary stock).

In principle each gvkey must have the primary stock and then the others.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey str12 isin str4 iid long datadate
"313082" "HK0000112026" "01W" 20454
"333147" "MU0471I00006" "01W" 20454
"200477" "BMG6982M1038" "01W" 20454
"212760" "JP3463100002" "01W" 20454
"221499" "DK0010219070" "02W" 20454
"103078" ""             "04W" 20454
"209577" "JP3938600008" "01W" 20454
"243695" "KR7018120006" "01W" 20454
"025648" ""             "05W" 20454
"318879" "ID1000133200" "01W" 20454
"292464" "CNE100000F95" "01W" 20454
"287270" "TH0954010R16" "90W" 20454
"242336" "AU000000CLH1" "01W" 20454
"253404" "AU000000TOX6" "01W" 20454
"285343" ""             "02W" 20454
"207132" "ZAE000172870" "01W" 20454
"209961" "ZAE000055802" "01W" 20454
"221859" "GB0007370074" "01W" 20454
"286871" "MA0000011710" "01W" 20454
"207565" "JP3630550006" "01W" 20454
"285589" "SG1BH2000007" "01W" 20454
"207172" ""             "03W" 20454
"295257" "RU000A0JPNP4" "01W" 20454
"287760" "AU000000BSR7" "01W" 20454
"315646" "NO0010657448" "01W" 20454
"315863" "GG00BBHX2H91" "01W" 20454
"284969" "SE0002457796" "90W" 20454
"216861" "ES0118594417" "01W" 20454
"208224" "NO0003028904" "01W" 20454
"312082" "CNE100001CS1" "01W" 20454
"100644" ""             "02W" 20454
"101509" "MYL4588OO009" "02W" 20454
"207172" "CNE000000552" "02W" 20454
"288005" "VN000000HRC9" "01W" 20454
"260219" ""             "02W" 20454
"245936" "ID1000094501" "03W" 20454
"220133" "JP3739600009" "01W" 20454
"314848" "INE323K01017" "01W" 20454
"270397" ""             "02W" 20454
"284473" "LV0000100881" "01W" 20454
"101932" "JP3240400006" "01W" 20454
"204861" "PK0065001015" "01W" 20454
"289918" "BD0718SALI06" "01W" 20454
"315278" "VN000000SVT2" "01W" 20454
"295377" "VN000000IDJ2" "01W" 20454
"279063" "TW0006123003" "01W" 20454
"297689" "VN000000DAE0" "01W" 20454
"285877" "CNE1000005P7" "01W" 20454
"270699" "CNE000001K65" "01W" 20454
"297054" "LU0292100046" "01W" 20454
"272996" "BD0206ESTCB6" "01W" 20454
"257933" "NZASBD0001S7" "01W" 20454
"289822" "INE828G01013" "01W" 20454
"202796" "JO4104211019" "01W" 20454
"278938" "TW0006175003" "01W" 20454
"100751" ""             "04W" 20454
"212979" "JP3875000006" "01W" 20454
"282447" ""             "02W" 20454
"290274" "PLHRTIM00013" "01W" 20454
"301992" "INE765C01024" "01W" 20454
"324120" "SK1120005071" "01W" 20454
"318067" "CNE100001TD7" "01W" 20454
"271187" "PLPKO0000016" "01W" 20454
"223139" "JP3225500002" "01W" 20454
"315821" "INE997B01025" "01W" 20454
"315094" "BD0486OPL004" "01W" 20454
"286422" "SE0001593914" "01W" 20454
"218461" "JP3233100001" "01W" 20454
"208099" "TW0001419000" "01W" 20454
"203664" "BMG8438Y1030" "01W" 20454
"317237" "ES0105015012" "01W" 20454
"100166" ""             "02W" 20454
"297414" "INE883C01025" "01W" 20454
"286701" "KR7051360006" "01W" 20454
"243061" "GRS331043000" "01W" 20454
"293872" "KYG8072W1087" "01W" 20454
"243128" "GRS277023008" "01W" 20454
"297391" "DE000A0KPMZ7" "01W" 20454
"253438" ""             "01W" 20454
"252561" "KYG3947G1313" "02W" 20454
"260010" "CNE000001FW7" "01W" 20454
"104888" "JP3710000005" "01W" 20454
"007114" ""             "02W" 20454
"316632" "INE319N01019" "01W" 20454
"101883" "JP3103600007" "01W" 20454
"297503" "INE170D01025" "01W" 20454
"212743" "JP3100630007" "01W" 20454
"204817" "INE050A01025" "01W" 20454
"295721" "INE482J01021" "01W" 20454
"321182" "LK0440N00004" "01W" 20454
"312900" "INE608B01010" "01W" 20454
"242146" "INE571A01038" "01W" 20454
"212737" "JP3166950000" "01W" 20454
"319121" ""             "02W" 20454
"015688" "MYL1155OO000" "01W" 20454
"296706" "NL0009690619" "01W" 20454
"316867" "KYG9302L1106" "01W" 20454
"010846" ""             "12W" 20454
"202894" "KE0000000216" "01W" 20454
"271360" "GB00B02H2F76" "01W" 20454
end
format %td datadate

[William Lisowski]

I think this example will point you in a useful direction.

. by gvkey (iid), sort: generate wanted = iid[1]!="01W"

. list if wanted, sepby(gvkey) 

     +--------------------------------------------------+
     |  gvkey           isin   iid    datadate   wanted |
     |--------------------------------------------------|
  1. | 007114                  02W   01jan2016        1 |
     |--------------------------------------------------|
  2. | 010846                  12W   01jan2016        1 |
     |--------------------------------------------------|
  4. | 025648                  05W   01jan2016        1 |
     |--------------------------------------------------|
  5. | 100166                  02W   01jan2016        1 |
     |--------------------------------------------------|
  6. | 100644                  02W   01jan2016        1 |
     |--------------------------------------------------|
  7. | 100751                  04W   01jan2016        1 |
     |--------------------------------------------------|
  8. | 101509   MYL4588OO009   02W   01jan2016        1 |
     |--------------------------------------------------|
 11. | 103078                  04W   01jan2016        1 |
     |--------------------------------------------------|
 20. | 207172   CNE000000552   02W   01jan2016        1 |
 21. | 207172                  03W   01jan2016        1 |
     |--------------------------------------------------|
 34. | 221499   DK0010219070   02W   01jan2016        1 |
     |--------------------------------------------------|
 42. | 245936   ID1000094501   03W   01jan2016        1 |
     |--------------------------------------------------|
 43. | 252561   KYG3947G1313   02W   01jan2016        1 |
     |--------------------------------------------------|
 48. | 260219                  02W   01jan2016        1 |
     |--------------------------------------------------|
 49. | 270397                  02W   01jan2016        1 |
     |--------------------------------------------------|
 56. | 282447                  02W   01jan2016        1 |
     |--------------------------------------------------|
 58. | 284969   SE0002457796   90W   01jan2016        1 |
     |--------------------------------------------------|
 59. | 285343                  02W   01jan2016        1 |
     |--------------------------------------------------|
 65. | 287270   TH0954010R16   90W   01jan2016        1 |
     |--------------------------------------------------|
 97. | 319121                  02W   01jan2016        1 |
     +--------------------------------------------------+

[Marco Errico]

Thank you William Lisowski . It works smoothly

May I kindly ask you one additional help.

I see now that for some "gvkey" with mupltiple "iid", it might that only one isin is populated.

i.e. I can have an example like the following one:

gvkey	iid	isin
1	01w	AAA
1	02w	missing
1	03w	missing

How can I create a variable that tells me that for same gvkey and multiple iid, we have information for only one isin?

[William Lisowski]

And I again think this new example will point you in a useful direction. The variable shows all the observations for every gvkey that has one or more missing observations with isin missing. The data for gvkey 20172 is like the example you described: multiple iids but some don't have isin. The data for gvkey 25348 is also interesting: even though there's only one iid, for the primary stock, it has a missing isin.

. by gvkey (iid), sort: egen wanted = max(missing(isin))

. list if wanted, sepby(gvkey) 

     +--------------------------------------------------+
     |  gvkey           isin   iid    datadate   wanted |
     |--------------------------------------------------|
  1. | 007114                  02W   01jan2016        1 |
     |--------------------------------------------------|
  2. | 010846                  12W   01jan2016        1 |
     |--------------------------------------------------|
  4. | 025648                  05W   01jan2016        1 |
     |--------------------------------------------------|
  5. | 100166                  02W   01jan2016        1 |
     |--------------------------------------------------|
  6. | 100644                  02W   01jan2016        1 |
     |--------------------------------------------------|
  7. | 100751                  04W   01jan2016        1 |
     |--------------------------------------------------|
 11. | 103078                  04W   01jan2016        1 |
     |--------------------------------------------------|
 20. | 207172   CNE000000552   02W   01jan2016        1 |
 21. | 207172                  03W   01jan2016        1 |
     |--------------------------------------------------|
 45. | 253438                  01W   01jan2016        1 |
     |--------------------------------------------------|
 48. | 260219                  02W   01jan2016        1 |
     |--------------------------------------------------|
 49. | 270397                  02W   01jan2016        1 |
     |--------------------------------------------------|
 56. | 282447                  02W   01jan2016        1 |
     |--------------------------------------------------|
 59. | 285343                  02W   01jan2016        1 |
     |--------------------------------------------------|
 97. | 319121                  02W   01jan2016        1 |
     +--------------------------------------------------+

[Marco Errico]

Thank you William Lisowski for your help and guidance.

Your suggestions have been really helpful