Alligning variables

[Hugo Rocha]

Hello,

I have the following dataset (below the dataex) and I want to create some variables such as labor productivity:

 gen labor_prod= GrossVal_basic/ _Number_of_employees

And I get a result zero observations though I have observations for Number of employees and Gross Value Added for some countries and industries, in the same years. Any hint on how I can reshape the data so as to be able to calculate the variables?

* Example generated by -dataex-. For more info, type help dataex
clear
input str15 Country str173 Industry str7 IND int Time float GrossVal_basic double _Number_of_employees
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001     . 190.492
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001  8218       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002     . 190.779
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002 10253       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003     . 181.698
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003 10404       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004     . 193.826
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004  9383       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005     . 202.366
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005  6896       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006     . 200.556
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006  8010       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007     . 193.565
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007  7968       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008     . 190.357
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008  9890       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009     . 178.396
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009  9570       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001     .  197.48
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001  8493       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002     . 197.806
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002 10488       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003     . 188.213
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003 10639       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004     . 200.849
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004  9649       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005     .  209.73
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005  7141       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006     . 207.618
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006  8274       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007     . 200.764
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007  8302       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008     . 199.795
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008 10318       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009     . 188.516
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009 10079       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     . 203.192
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001  8932       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     . 203.509
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002 10908       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     . 193.512
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003 11004       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .  206.65
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004 10055       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     . 215.615
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005  7635       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2006     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2006     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2006     .       .
end.

[Ken Chui]

It is because there is no row that has valid gross value and number of employees, so the calculation will result in missing values.

The creation process of this data set should be re-examined. It's likely that someone got a data set on gross value and a data set on employee number, and while trying combine them they mistakenly used append rather than merge. I'd suggest going back to the data structuring stage and make sure it's being built correctly.

Assuming they are Country, Industry, IND, and Year are unique identifier, it's possible to collapse them into unique rows:

collapse (mean) GrossVal_basic _Number_of_employees, by(Country Industry IND Time)
generate labor_prod= GrossVal_basic/ _Number_of_employees

Results:

     +---------------------------------------------------------------------------------------------------------------------------------------+
     |        Country                                                              Industry      IND   Time   GrossV~c   _Numbe~s   labor_~d |
     |---------------------------------------------------------------------------------------------------------------------------------------|
  1. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2001       8493     197.48   43.00689 |
  2. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2002      10488    197.806   53.02165 |
  3. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2003      10639    188.213   56.52638 |
  4. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2004       9649    200.849   48.04107 |
  5. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2005       7141     209.73   34.04854 |
  6. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2006       8274    207.618   39.85204 |
  7. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2007       8302    200.764   41.35204 |
  8. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2008      10318    199.795   51.64293 |
  9. | United Kingdom                                     Agriculture, hunting and forestry   D01T02   2009      10079    188.516   53.46496 |
     |---------------------------------------------------------------------------------------------------------------------------------------|
 10. | United Kingdom                        Agriculture, hunting, forestry and fishing [A]   D01T03   2001       8932    203.192   43.95842 |
 11. | United Kingdom                        Agriculture, hunting, forestry and fishing [A]   D01T03   2002      10908    203.509   53.59959 |
 12. | United Kingdom                        Agriculture, hunting, forestry and fishing [A]   D01T03   2003      11004    193.512   56.86469 |
 13. | United Kingdom                        Agriculture, hunting, forestry and fishing [A]   D01T03   2004      10055     206.65   48.65715 |
 14. | United Kingdom                        Agriculture, hunting, forestry and fishing [A]   D01T03   2005       7635    215.615   35.41034 |
 15. | United Kingdom                        Agriculture, hunting, forestry and fishing [A]   D01T03   2006          .          .          . |
     |---------------------------------------------------------------------------------------------------------------------------------------|
 16. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2001       8218    190.492   43.14092 |
 17. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2002      10253    190.779   53.74281 |
 18. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2003      10404    181.698   57.25985 |
 19. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2004       9383    193.826    48.4094 |
 20. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2005       6896    202.366   34.07687 |
 21. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2006       8010    200.556   39.93897 |
 22. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2007       7968    193.565   41.16447 |
 23. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2008       9890    190.357   51.95501 |
 24. | United Kingdom    Crop and animal production, hunting and related service activities      D01   2009       9570    178.396    53.6447 |
     +---------------------------------------------------------------------------------------------------------------------------------------+

Yet, as I said, I don't know the data structure clearly so this is just one of many possible ways to solve it.

[Hugo Rocha]

Thank you very much. I think I realized what the problem is. I downloaded all the dataset and grouped industries with different level of disaggregation. For instance, in the example you provided:

 * Example generated by -dataex-. For more info, type help dataex
clear
input str15 Country str173 Industry str7 IND int Time float GrossVal_basic double _Number_of_employees
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001     . 190.492
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2001  8218       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002     . 190.779
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2002 10253       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003     . 181.698
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2003 10404       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004     . 193.826
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2004  9383       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005     . 202.366
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2005  6896       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006     . 200.556
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2006  8010       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007     . 193.565
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2007  7968       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008     . 190.357
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2008  9890       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009     .       .
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009     . 178.396
"United Kingdom" " Crop and animal production, hunting and related service activities" "D01"    2009  9570       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001     .  197.48
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2001  8493       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002     . 197.806
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2002 10488       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003     . 188.213
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2003 10639       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004     . 200.849
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2004  9649       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005     .  209.73
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2005  7141       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006     . 207.618
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2006  8274       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007     . 200.764
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2007  8302       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008     . 199.795
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2008 10318       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009     .       .
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009     . 188.516
"United Kingdom" " Agriculture, hunting and forestry"                                  "D01T02" 2009 10079       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001     . 203.192
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2001  8932       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002     . 203.509
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2002 10908       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003     . 193.512
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2003 11004       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004     .  206.65
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2004 10055       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005     . 215.615
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2005  7635       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2006     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2006     .       .
"United Kingdom" " Agriculture, hunting, forestry and fishing [A]"                     "D01T03" 2006     .       .
end

You have D01, D01T03, and MHDI. Clearly three different leves of disaggregation. The answer is actually collapsing but using the same level of disaggregation. Do you have a particular suggestion? Thanks!

tab IND

        IND |      Freq.     Percent        Cum.
------------+-----------------------------------
        D01 |        165        0.48        0.48
     D01T02 |        165        0.48        0.96
     D01T03 |        710        2.06        3.02
     D01T99 |        831        2.41        5.44
        D02 |        165        0.48        5.91
        D03 |        160        0.46        6.38
     D05T06 |         62        0.18        6.56
     D05T09 |        663        1.93        8.49
     D05T39 |        694        2.02       10.50
    D05T82X |        380        1.10       11.61
     D07T08 |         68        0.20       11.80
        D09 |        107        0.31       12.11
        D10 |        100        0.29       12.40
     D10T11 |         78        0.23       12.63
     D10T12 |        523        1.52       14.15
     D10T33 |        663        1.93       16.08
        D11 |         78        0.23       16.30
        D12 |         62        0.18       16.48
        D13 |        101        0.29       16.78
     D13T14 |         68        0.20       16.97
     D13T15 |        523        1.52       18.49
        D14 |         68        0.20       18.69
        D15 |         68        0.20       18.89
        D16 |        194        0.56       19.45
     D16T18 |        523        1.52       20.97
        D17 |        194        0.56       21.53
        D18 |        198        0.58       22.11
        D19 |        474        1.38       23.49
     D19T23 |        507        1.47       24.96
        D20 |        402        1.17       26.13
     D20T21 |        469        1.36       27.49
        D21 |        397        1.15       28.64
        D22 |        194        0.56       29.21
     D22T23 |        485        1.41       30.62
        D23 |        198        0.58       31.19
        D24 |        205        0.60       31.79
     D24T25 |        523        1.52       33.31
        D25 |        205        0.60       33.90
        D26 |        395        1.15       35.05
     D26T27 |        493        1.43       36.48
     D26T28 |        519        1.51       37.99
        D27 |        395        1.15       39.14
        D28 |        493        1.43       40.57
        D29 |        163        0.47       41.04
     D29T30 |        519        1.51       42.55
        D30 |        158        0.46       43.01
     D31T32 |        151        0.44       43.45
     D31T33 |        523        1.52       44.97
        D33 |        135        0.39       45.36
        D35 |        346        1.01       46.36
     D35T39 |        658        1.91       48.27
        D36 |        136        0.40       48.67
     D36T39 |        348        1.01       49.68
     D37T39 |        136        0.40       50.08
     D41T43 |        699        2.03       52.11
        D45 |        145        0.42       52.53
     D45T47 |        433        1.26       53.79
     D45T56 |        445        1.29       55.08
     D45T82 |        699        2.03       57.11
    D45T82X |        385        1.12       58.23
     D45T99 |        710        2.06       60.29
        D46 |        154        0.45       60.74
        D47 |        150        0.44       61.17
        D49 |        153        0.44       61.62
     D49T53 |        444        1.29       62.91
        D50 |        164        0.48       63.38
        D51 |        164        0.48       63.86
        D52 |        157        0.46       64.32
        D53 |        146        0.42       64.74
        D55 |         90        0.26       65.00
     D55T56 |        444        1.29       66.29
        D56 |         90        0.26       66.55
        D58 |        157        0.46       67.01
     D58T60 |        192        0.56       67.57
     D58T63 |        423        1.23       68.80
     D59T60 |        135        0.39       69.19
        D61 |        183        0.53       69.72
        D62 |         66        0.19       69.91
     D62T63 |        205        0.60       70.51
        D63 |         66        0.19       70.70
        D64 |        145        0.42       71.12
     D64T66 |        445        1.29       72.41
        D65 |        145        0.42       72.83
        D66 |        140        0.41       73.24
        D68 |        396        1.15       74.39
       D68A |        178        0.52       74.91
     D68T82 |        434        1.26       76.17
        D69 |         90        0.26       76.43
     D69T70 |        157        0.46       76.89
     D69T71 |        182        0.53       77.41
     D69T75 |        330        0.96       78.37
     D69T82 |        396        1.15       79.52
        D70 |         90        0.26       79.78
        D71 |        135        0.39       80.18
        D72 |        182        0.53       80.71
        D73 |        135        0.39       81.10
     D73T75 |        182        0.53       81.63
        D74 |         68        0.20       81.82
     D74T75 |        135        0.39       82.22
        D75 |         62        0.18       82.40
        D77 |        167        0.49       82.88
     D77T82 |        326        0.95       83.83
        D78 |        135        0.39       84.22
        D79 |        135        0.39       84.61
     D80T82 |        135        0.39       85.00
        D84 |        430        1.25       86.25
     D84T88 |        465        1.35       87.60
     D84T99 |        699        2.03       89.64
        D85 |        441        1.28       90.92
        D86 |        204        0.59       91.51
     D86T88 |        441        1.28       92.79
     D87T88 |        182        0.53       93.32
     D90T92 |        135        0.39       93.71
     D90T93 |        326        0.95       94.66
     D90T99 |        465        1.35       96.01
        D93 |        135        0.39       96.40
        D94 |        135        0.39       96.79
     D94T96 |        304        0.88       97.68
        D95 |        135        0.39       98.07
        D96 |        135        0.39       98.46
     D97T98 |        290        0.84       99.30
        D99 |        240        0.70      100.00
------------+-----------------------------------
      Total |     34,424      100.00.