Problem to give groupid for an id by firm

[Manuel Matte]

Hi, i'm working with Panel Data. I'm having a problem to unify an ID, named idgrupo, that basically is a variable that agroup every firm that are part of the same business group (0 for those that on a certain moment of time doesn't belong to any). The problem is that when i made the database, in different moment of time the groupid is out of phase for the same firm. For example, Aguas Araucanias that is in the same group with Aguas del Altiplano on 2018yq groupid 86, on 2019q1 they are on groupid 85. I need to unify this, to obtain that Aguas Araucanias is always on groupid 86.

Thank you very much.

* Example generated by -dataex-. To install: ssc install dataex
clear
input float newid str50 nombre float yq int idgrupo
1 "Aguas Araucania S.A."                          235  86
1 "Aguas Araucania S.A."                          236  85
1 "Aguas Araucania S.A."                          237  85
1 "Aguas Araucania S.A."                          239  84
2 "Sociedad Concesionaria Autopista Central S.A." 200   0
2 "Sociedad Concesionaria Autopista Central S.A." 201   0
2 "Sociedad Concesionaria Autopista Central S.A." 202   0
2 "Sociedad Concesionaria Autopista Central S.A." 203   0
2 "Sociedad Concesionaria Autopista Central S.A." 204   0
2 "Sociedad Concesionaria Autopista Central S.A." 206   0
2 "Sociedad Concesionaria Autopista Central S.A." 207   0
2 "Sociedad Concesionaria Autopista Central S.A." 208   0
2 "Sociedad Concesionaria Autopista Central S.A." 209   0
2 "Sociedad Concesionaria Autopista Central S.A." 210   0
2 "Sociedad Concesionaria Autopista Central S.A." 211   0
2 "Sociedad Concesionaria Autopista Central S.A." 212   0
2 "Sociedad Concesionaria Autopista Central S.A." 213   0
2 "Sociedad Concesionaria Autopista Central S.A." 214   0
2 "Sociedad Concesionaria Autopista Central S.A." 215   0
2 "Sociedad Concesionaria Autopista Central S.A." 216   0
2 "Sociedad Concesionaria Autopista Central S.A." 217   0
2 "Sociedad Concesionaria Autopista Central S.A." 218   0
2 "Sociedad Concesionaria Autopista Central S.A." 219   0
2 "Sociedad Concesionaria Autopista Central S.A." 220   0
2 "Sociedad Concesionaria Autopista Central S.A." 221   0
2 "Sociedad Concesionaria Autopista Central S.A." 222   0
2 "Sociedad Concesionaria Autopista Central S.A." 223   0
2 "Sociedad Concesionaria Autopista Central S.A." 224   0
2 "Sociedad Concesionaria Autopista Central S.A." 225   0
2 "Sociedad Concesionaria Autopista Central S.A." 226   0
2 "Sociedad Concesionaria Autopista Central S.A." 227   0
2 "Sociedad Concesionaria Autopista Central S.A." 228   0
2 "Sociedad Concesionaria Autopista Central S.A." 229   0
2 "Sociedad Concesionaria Autopista Central S.A." 230   0
2 "Sociedad Concesionaria Autopista Central S.A." 231   0
2 "Sociedad Concesionaria Autopista Central S.A." 232   0
2 "Sociedad Concesionaria Autopista Central S.A." 234   0
2 "Sociedad Concesionaria Autopista Central S.A." 238   0
3 "Aguas Del Altiplano S.A."                      235  86
3 "Aguas Del Altiplano S.A."                      236  85
3 "Aguas Del Altiplano S.A."                      237  85
3 "Aguas Del Altiplano S.A."                      238  84
3 "Aguas Del Altiplano S.A."                      239  84
4 "Ad Retail S.A."                                208   0
4 "Ad Retail S.A."                                209   0
4 "Ad Retail S.A."                                210   0
4 "Ad Retail S.A."                                211   0
4 "Ad Retail S.A."                                212   0
4 "Ad Retail S.A."                                213   0
4 "Ad Retail S.A."                                214   0
4 "Ad Retail S.A."                                215   0
4 "Ad Retail S.A."                                216   0
4 "Ad Retail S.A."                                217   0
4 "Ad Retail S.A."                                218   0
4 "Ad Retail S.A."                                219   0
4 "Ad Retail S.A."                                220 122
4 "Ad Retail S.A."                                221 119
4 "Ad Retail S.A."                                222 121
4 "Ad Retail S.A."                                223 117
4 "Ad Retail S.A."                                224 119
4 "Ad Retail S.A."                                225 114
4 "Ad Retail S.A."                                226 113
4 "Ad Retail S.A."                                227 110
4 "Ad Retail S.A."                                228 110
4 "Ad Retail S.A."                                229 110
4 "Ad Retail S.A."                                230 109
4 "Ad Retail S.A."                                231 109
4 "Ad Retail S.A."                                232 109
4 "Ad Retail S.A."                                233 108
4 "Ad Retail S.A."                                234 103
4 "Ad Retail S.A."                                236 102
4 "Ad Retail S.A."                                238 101
4 "Ad Retail S.A."                                239 101
5 "Autofin S.A."                                  239   0
6 "Agrosuper S.A."                                208   0
6 "Agrosuper S.A."                                209   0
6 "Agrosuper S.A."                                210   0
6 "Agrosuper S.A."                                211   0
6 "Agrosuper S.A."                                212   0
6 "Agrosuper S.A."                                213   0
6 "Agrosuper S.A."                                214   0
6 "Agrosuper S.A."                                215   0
6 "Agrosuper S.A."                                216   0
6 "Agrosuper S.A."                                217   0
6 "Agrosuper S.A."                                218   0
6 "Agrosuper S.A."                                219   0
6 "Agrosuper S.A."                                220   0
6 "Agrosuper S.A."                                221   0
6 "Agrosuper S.A."                                222   0
6 "Agrosuper S.A."                                223   0
6 "Agrosuper S.A."                                224   0
6 "Agrosuper S.A."                                225   0
6 "Agrosuper S.A."                                226   0
6 "Agrosuper S.A."                                227   0
6 "Agrosuper S.A."                                228   0
6 "Agrosuper S.A."                                229   0
6 "Agrosuper S.A."                                230   0
6 "Agrosuper S.A."                                231   0
6 "Agrosuper S.A."                                232   0
6 "Agrosuper S.A."                                234   0
end
format %tq yq

[Andrew Musau]

Install group_id from SSC by Robert Picard.

ssc install group_id, replace

gen id= cond(!idgrupo, -_n, idgrupo)
group_id id, matchby(nombre)
replace id=0 if id<0

Res.:

. l, sepby(id)

     +-------------------------------------------------------------------------------+
     | newid                                          nombre       yq   idgrupo   id |
     |-------------------------------------------------------------------------------|
  1. |     6                                  Agrosuper S.A.   2018q3         0    0 |
  2. |     6                                  Agrosuper S.A.   2018q1         0    0 |
  3. |     6                                  Agrosuper S.A.   2017q4         0    0 |
  4. |     6                                  Agrosuper S.A.   2017q3         0    0 |
  5. |     6                                  Agrosuper S.A.   2017q2         0    0 |
  6. |     6                                  Agrosuper S.A.   2017q1         0    0 |
  7. |     6                                  Agrosuper S.A.   2016q4         0    0 |
  8. |     6                                  Agrosuper S.A.   2016q3         0    0 |
  9. |     6                                  Agrosuper S.A.   2016q2         0    0 |
 10. |     6                                  Agrosuper S.A.   2016q1         0    0 |
 11. |     6                                  Agrosuper S.A.   2015q4         0    0 |
 12. |     6                                  Agrosuper S.A.   2015q3         0    0 |
 13. |     6                                  Agrosuper S.A.   2015q2         0    0 |
 14. |     6                                  Agrosuper S.A.   2015q1         0    0 |
 15. |     6                                  Agrosuper S.A.   2014q4         0    0 |
 16. |     6                                  Agrosuper S.A.   2014q3         0    0 |
 17. |     6                                  Agrosuper S.A.   2014q2         0    0 |
 18. |     6                                  Agrosuper S.A.   2014q1         0    0 |
 19. |     6                                  Agrosuper S.A.   2013q4         0    0 |
 20. |     6                                  Agrosuper S.A.   2013q3         0    0 |
 21. |     6                                  Agrosuper S.A.   2013q2         0    0 |
 22. |     6                                  Agrosuper S.A.   2013q1         0    0 |
 23. |     6                                  Agrosuper S.A.   2012q4         0    0 |
 24. |     6                                  Agrosuper S.A.   2012q3         0    0 |
 25. |     6                                  Agrosuper S.A.   2012q2         0    0 |
 26. |     6                                  Agrosuper S.A.   2012q1         0    0 |
 27. |     5                                    Autofin S.A.   2019q4         0    0 |
 28. |     4                                  Ad Retail S.A.   2014q4         0    0 |
 29. |     4                                  Ad Retail S.A.   2014q3         0    0 |
 30. |     4                                  Ad Retail S.A.   2014q2         0    0 |
 31. |     4                                  Ad Retail S.A.   2014q1         0    0 |
 32. |     4                                  Ad Retail S.A.   2013q4         0    0 |
 33. |     4                                  Ad Retail S.A.   2013q3         0    0 |
 34. |     4                                  Ad Retail S.A.   2013q2         0    0 |
 35. |     4                                  Ad Retail S.A.   2013q1         0    0 |
 36. |     4                                  Ad Retail S.A.   2012q4         0    0 |
 37. |     4                                  Ad Retail S.A.   2012q3         0    0 |
 38. |     4                                  Ad Retail S.A.   2012q2         0    0 |
 39. |     4                                  Ad Retail S.A.   2012q1         0    0 |
 40. |     2   Sociedad Concesionaria Autopista Central S.A.   2019q3         0    0 |
 41. |     2   Sociedad Concesionaria Autopista Central S.A.   2018q3         0    0 |
 42. |     2   Sociedad Concesionaria Autopista Central S.A.   2018q1         0    0 |
 43. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q4         0    0 |
 44. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q3         0    0 |
 45. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q2         0    0 |
 46. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q1         0    0 |
 47. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q4         0    0 |
 48. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q3         0    0 |
 49. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q2         0    0 |
 50. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q1         0    0 |
 51. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q4         0    0 |
 52. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q3         0    0 |
 53. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q2         0    0 |
 54. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q1         0    0 |
 55. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q4         0    0 |
 56. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q3         0    0 |
 57. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q2         0    0 |
 58. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q1         0    0 |
 59. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q4         0    0 |
 60. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q3         0    0 |
 61. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q2         0    0 |
 62. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q1         0    0 |
 63. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q4         0    0 |
 64. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q3         0    0 |
 65. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q2         0    0 |
 66. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q1         0    0 |
 67. |     2   Sociedad Concesionaria Autopista Central S.A.   2011q4         0    0 |
 68. |     2   Sociedad Concesionaria Autopista Central S.A.   2011q3         0    0 |
 69. |     2   Sociedad Concesionaria Autopista Central S.A.   2011q1         0    0 |
 70. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q4         0    0 |
 71. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q3         0    0 |
 72. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q2         0    0 |
 73. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q1         0    0 |
     |-------------------------------------------------------------------------------|
 74. |     1                            Aguas Araucania S.A.   2019q4        84   84 |
 75. |     3                        Aguas Del Altiplano S.A.   2019q4        84   84 |
 76. |     3                        Aguas Del Altiplano S.A.   2019q3        84   84 |
 77. |     3                        Aguas Del Altiplano S.A.   2019q2        85   84 |
 78. |     3                        Aguas Del Altiplano S.A.   2019q1        85   84 |
 79. |     1                            Aguas Araucania S.A.   2019q1        85   84 |
 80. |     1                            Aguas Araucania S.A.   2019q2        85   84 |
 81. |     1                            Aguas Araucania S.A.   2018q4        86   84 |
 82. |     3                        Aguas Del Altiplano S.A.   2018q4        86   84 |
     |-------------------------------------------------------------------------------|
 83. |     4                                  Ad Retail S.A.   2019q3       101    0 |
 84. |     4                                  Ad Retail S.A.   2019q4       101    0 |
 85. |     4                                  Ad Retail S.A.   2019q1       102    0 |
 86. |     4                                  Ad Retail S.A.   2018q3       103    0 |
 87. |     4                                  Ad Retail S.A.   2018q2       108    0 |
 88. |     4                                  Ad Retail S.A.   2017q3       109    0 |
 89. |     4                                  Ad Retail S.A.   2018q1       109    0 |
 90. |     4                                  Ad Retail S.A.   2017q4       109    0 |
 91. |     4                                  Ad Retail S.A.   2016q4       110    0 |
 92. |     4                                  Ad Retail S.A.   2017q2       110    0 |
 93. |     4                                  Ad Retail S.A.   2017q1       110    0 |
 94. |     4                                  Ad Retail S.A.   2016q3       113    0 |
 95. |     4                                  Ad Retail S.A.   2016q2       114    0 |
 96. |     4                                  Ad Retail S.A.   2015q4       117    0 |
 97. |     4                                  Ad Retail S.A.   2015q2       119    0 |
 98. |     4                                  Ad Retail S.A.   2016q1       119    0 |
 99. |     4                                  Ad Retail S.A.   2015q3       121    0 |
100. |     4                                  Ad Retail S.A.   2015q1       122    0 |
     +-------------------------------------------------------------------------------+

.

[Manuel Matte]

Thanks Andrew! But, if you can see in the answer that you send me, Ad Retail S.A has a 0 on id on periods while the firm is still on a business group.

[Andrew Musau]

Just run the command excluding observations with idgrupo=0 and merge back with original. The confusion arises from the fact that you have idgrupo=0 for some observations of nombre "Ad Retail S.A.", so in essence, the firm should be grouped with the other firms with idgrupo=0.

frame put nombre idgrupo if idgrupo, into(new)
frame new{
    group_id idgrupo, matchby(nombre)
    contract nombre idgrupo
    rename idgrupo id
}
frlink m:1 nombre, frame(new)
frget id, from(new)
frame drop new

Res.:

. sort id

. l newid nombre yq idgrupo id , sepby(id)

     +--------------------------------------------------------------------------------+
     | newid                                          nombre       yq   idgrupo    id |
     |--------------------------------------------------------------------------------|
  1. |     3                        Aguas Del Altiplano S.A.   2019q2        85    84 |
  2. |     1                            Aguas Araucania S.A.   2019q2        85    84 |
  3. |     3                        Aguas Del Altiplano S.A.   2019q1        85    84 |
  4. |     1                            Aguas Araucania S.A.   2019q1        85    84 |
  5. |     3                        Aguas Del Altiplano S.A.   2019q3        84    84 |
  6. |     3                        Aguas Del Altiplano S.A.   2018q4        86    84 |
  7. |     1                            Aguas Araucania S.A.   2018q4        86    84 |
  8. |     3                        Aguas Del Altiplano S.A.   2019q4        84    84 |
  9. |     1                            Aguas Araucania S.A.   2019q4        84    84 |
     |--------------------------------------------------------------------------------|
 10. |     4                                  Ad Retail S.A.   2013q3         0   101 |
 11. |     4                                  Ad Retail S.A.   2012q2         0   101 |
 12. |     4                                  Ad Retail S.A.   2017q2       110   101 |
 13. |     4                                  Ad Retail S.A.   2017q3       109   101 |
 14. |     4                                  Ad Retail S.A.   2013q1         0   101 |
 15. |     4                                  Ad Retail S.A.   2015q2       119   101 |
 16. |     4                                  Ad Retail S.A.   2012q4         0   101 |
 17. |     4                                  Ad Retail S.A.   2013q2         0   101 |
 18. |     4                                  Ad Retail S.A.   2018q3       103   101 |
 19. |     4                                  Ad Retail S.A.   2017q4       109   101 |
 20. |     4                                  Ad Retail S.A.   2012q3         0   101 |
 21. |     4                                  Ad Retail S.A.   2016q3       113   101 |
 22. |     4                                  Ad Retail S.A.   2019q1       102   101 |
 23. |     4                                  Ad Retail S.A.   2014q4         0   101 |
 24. |     4                                  Ad Retail S.A.   2016q1       119   101 |
 25. |     4                                  Ad Retail S.A.   2019q4       101   101 |
 26. |     4                                  Ad Retail S.A.   2014q2         0   101 |
 27. |     4                                  Ad Retail S.A.   2018q2       108   101 |
 28. |     4                                  Ad Retail S.A.   2014q1         0   101 |
 29. |     4                                  Ad Retail S.A.   2016q2       114   101 |
 30. |     4                                  Ad Retail S.A.   2015q3       121   101 |
 31. |     4                                  Ad Retail S.A.   2017q1       110   101 |
 32. |     4                                  Ad Retail S.A.   2018q1       109   101 |
 33. |     4                                  Ad Retail S.A.   2013q4         0   101 |
 34. |     4                                  Ad Retail S.A.   2012q1         0   101 |
 35. |     4                                  Ad Retail S.A.   2015q1       122   101 |
 36. |     4                                  Ad Retail S.A.   2015q4       117   101 |
 37. |     4                                  Ad Retail S.A.   2014q3         0   101 |
 38. |     4                                  Ad Retail S.A.   2016q4       110   101 |
 39. |     4                                  Ad Retail S.A.   2019q3       101   101 |
     |--------------------------------------------------------------------------------|
 40. |     6                                  Agrosuper S.A.   2015q4         0     . |
 41. |     2   Sociedad Concesionaria Autopista Central S.A.   2019q3         0     . |
 42. |     2   Sociedad Concesionaria Autopista Central S.A.   2011q3         0     . |
 43. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q2         0     . |
 44. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q2         0     . |
 45. |     2   Sociedad Concesionaria Autopista Central S.A.   2011q4         0     . |
 46. |     6                                  Agrosuper S.A.   2012q4         0     . |
 47. |     6                                  Agrosuper S.A.   2013q2         0     . |
 48. |     6                                  Agrosuper S.A.   2016q4         0     . |
 49. |     6                                  Agrosuper S.A.   2012q3         0     . |
 50. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q1         0     . |
 51. |     6                                  Agrosuper S.A.   2013q3         0     . |
 52. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q2         0     . |
 53. |     6                                  Agrosuper S.A.   2012q1         0     . |
 54. |     6                                  Agrosuper S.A.   2015q1         0     . |
 55. |     6                                  Agrosuper S.A.   2016q2         0     . |
 56. |     6                                  Agrosuper S.A.   2017q4         0     . |
 57. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q1         0     . |
 58. |     2   Sociedad Concesionaria Autopista Central S.A.   2018q1         0     . |
 59. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q2         0     . |
 60. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q4         0     . |
 61. |     6                                  Agrosuper S.A.   2014q4         0     . |
 62. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q1         0     . |
 63. |     6                                  Agrosuper S.A.   2012q2         0     . |
 64. |     6                                  Agrosuper S.A.   2016q3         0     . |
 65. |     2   Sociedad Concesionaria Autopista Central S.A.   2011q1         0     . |
 66. |     5                                    Autofin S.A.   2019q4         0     . |
 67. |     6                                  Agrosuper S.A.   2018q1         0     . |
 68. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q3         0     . |
 69. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q1         0     . |
 70. |     6                                  Agrosuper S.A.   2013q1         0     . |
 71. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q3         0     . |
 72. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q3         0     . |
 73. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q1         0     . |
 74. |     2   Sociedad Concesionaria Autopista Central S.A.   2018q3         0     . |
 75. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q2         0     . |
 76. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q1         0     . |
 77. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q2         0     . |
 78. |     6                                  Agrosuper S.A.   2015q3         0     . |
 79. |     6                                  Agrosuper S.A.   2015q2         0     . |
 80. |     2   Sociedad Concesionaria Autopista Central S.A.   2017q2         0     . |
 81. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q3         0     . |
 82. |     6                                  Agrosuper S.A.   2014q3         0     . |
 83. |     6                                  Agrosuper S.A.   2014q2         0     . |
 84. |     2   Sociedad Concesionaria Autopista Central S.A.   2015q4         0     . |
 85. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q4         0     . |
 86. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q3         0     . |
 87. |     2   Sociedad Concesionaria Autopista Central S.A.   2014q4         0     . |
 88. |     6                                  Agrosuper S.A.   2017q2         0     . |
 89. |     2   Sociedad Concesionaria Autopista Central S.A.   2013q4         0     . |
 90. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q3         0     . |
 91. |     6                                  Agrosuper S.A.   2013q4         0     . |
 92. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q3         0     . |
 93. |     6                                  Agrosuper S.A.   2016q1         0     . |
 94. |     6                                  Agrosuper S.A.   2014q1         0     . |
 95. |     6                                  Agrosuper S.A.   2017q1         0     . |
 96. |     6                                  Agrosuper S.A.   2017q3         0     . |
 97. |     2   Sociedad Concesionaria Autopista Central S.A.   2010q1         0     . |
 98. |     2   Sociedad Concesionaria Autopista Central S.A.   2016q4         0     . |
 99. |     2   Sociedad Concesionaria Autopista Central S.A.   2012q4         0     . |
100. |     6                                  Agrosuper S.A.   2018q3         0     . |
     +--------------------------------------------------------------------------------+

.