Picking highest real output per worker in sector i in year t

[Hugo Rocha]

Dear all,

In my dataset, I have variables of real output per worker across 34 different sectors in approximately 50 years. In order to generate a variable, I need to pick the highest real output per worker in every of the 34 sectors for each particular year. I am pretty sure I need a for-loop before generating the variable. Is there a way I could get some guidance on how to start the for-loop and generate the variable? Please, find the dataex for the variables



[/*CODE]
* Example generated by -dataex-. To install: ssc install dataex
clear
input double(Wages Output ValueAdded) float(realtotalwages realtotalvalueadded realwageperworker realoutputperworker) long isic1
347826 2017391 . 1101770.6 . . . 11
5882798 8063997 6719997 18634300 21286206 2602.556 3567.521 20
1418254 26418463 5422737 4492449 17177016 10375.17 193263.05 9
310799 2018800 828801 984484.3 2625303 2316.4336 15046.432 11
. . . . . . . 23
602000 2492000 1190000 1906890 3769434 2011.4873 8326.622 22
4639286 42526787 19330358 14695363 61230676 9184.602 84192.18 2
589005 1832461 1361257 1865727.3 4311906 1680.8353 5229.269 8
. . . . . . . 18
30313953 190625807 87498908 96022216 277160800 7060.457 44398.88 10
7000 84000 42000 22173.14 133038.84 739.1047 8869.256 19
13074957 66439523 29812606 41416120 94434152 2436.2424 12379.604 8
7.400e+09 3.341e+10 1.439e+10 23440177152 45581639680 21906.707 98905.82 13
. . . . . . . 23
. . . . . . . 19
. . . . . . . 18
168000168 1447951448 443100443 532155904 1403561216 1673.4463 14423.015 13
353208 7396226 4996226 1118818.6 15826003 4885.671 102306.65 2
6923077 41538462 19230769 21929480 60915220 1423.9922 8543.953 8
. . . . . . . 18
. . . . . . . 21
. . . . . . . 11
. . . . . . . 5
458964835 1761683205 815937484 1453813120 2584556544 14114.69 54177.6 17
104938272 727777778 . 332401568 . 3462.516 24013.57 10
8183258 101523544 38103295 25921218 120695672 2254.019 27963.924 20
27999989 97999961 54599978 88692528 172950416 7712.394 26993.377 12
. . . . . . . 14
. . . . . . . 16
. 4427499355 . . . . 72816.766 1
. . . . . . . 5
423077 3076923 1230769 1340135 3898573 822.1688 5979.408 19
3846154 51538462 11538462 12183045 36549132 895.8121 12003.88 9
. . . . . . . 11
. . . . . . . 16
522190 3868963 1384012 1654084.6 4383984.5 1848.1393 13693.066 14
. . . . . . . 16
. . . . . . . 18
258833490 976183066 425267870 819878720 1347074816 11277.562 42533 20
10000000 61066667 25333333 31675914 80245648 5027.923 30703.85 6
217333333 1.329e+09 5.840e+08 688423232 1849873408 4469.7 27332.354 24
. . . . . . . 18
. . . . . . . 16
3940494 18825329 8746880 12481875 27706542 1358.6454 6490.797 22
781200 10259196 3401999 2474522.5 10776143 4063.2554 53361.15 9
. . . . . . . 4
. . . . . . . 23
109796432 409803584 187504470 347790240 593937536 8013.6 29909.916 3
. 2347348701 1178231582 . 3732156160 . 19764.81 17
34783 556522 . 110178.34 . . . 13
86520087 509670510 171990172 274060288 544794624 1323.9628 7799.168 15
1052799579 6299997480 2911998835 3334839040 9224023040 7510.899 44945.54 10
17611940 65671642 38805970 55787432 122921456 7621.234 28418.16 8
. . . . . . . 12
0 0 0 0 0 . . 20
417391 1947826 . 1322124 . . . 4
3.210e+09 1.233e+10 7.050e+09 10167968768 22331518976 17714.23 68042.516 12
375200 1402799 604800 1188480.3 1915759.3 1342.9155 5020.897 8
739200 6271997 2631999 2341483.5 8337098 3121.978 26489.5 10
75600 1204001 358400 239469.9 1135264.8 2238.0366 35642.836 10
246400 2038400 671999 780494.6 2128618.3 2001.268 16555.945 3
. . . . . . . 16
0 0 0 0 0 . . 17
727777778 5852777778 2252777778 2305302784 7135879680 3641.869 29287.85 20
4483504 14703795 9331569 14201909 29558598 1482.7635 4862.7705 8
. . . . . . . 22
1005200 2609599 1251599 3184063 3964554 646.7729 1679.0867 6
. . . . . . . 16
. . . . . . . 14
. . . . . . . 22
259000 1000000 310000 820406.2 981953.4 1827.1853 7054.77 14
. . . . . . . 8
891000 4740000 2020000 2822324 6398535 5588.76 29731.45 14
38000000 . 184266667 120368472 583681536 1744.4706 . 13
8261108333 65227750000 24744441667 26167816192 78380277760 2704.684 21355.54 24
. . . . . . . 21
. . . . . . . 23
4441504 32319872 9650980 14068870 30570362 988.3295 7191.862 13
3068722 25061227 10229072 9720458 32401520 1905.972 15565.437 13
. . . . . . . 9
7423151 104884165 16038156 23513510 50802324 6877.306 97171.74 9
21093057 132092314 53532148 66814188 169567968 9572.233 59944.77 7
. . . . . . . 1
968888889 . . 3069044224 . 3671.1055 . 6
2450004 16006690 6533343 7760612 20694962 3767.287 24612.94 20
. . . . . . . 5
4347356 45263645 15855062 13770648 50222360 2118.5613 22057.96 7
14328358 107164179 53731343 45386384 170198944 7416.076 55466.07 11
69565 417391 . 220353.5 . . . 7
. . . . . . . 16
45022500 . . 142612880 . 1821.365 . 15
. . . . . . . 23
158563668 707182270 256905771 502264928 813772544 4367.521 19478.822 4
. . . . . . . 13
392000 2455599 859600 1241695.9 2722861.5 1069.5055 6699.685 3
1121191 12943783 10796321 3551475 34198336 2219.6719 25625.385 9
56874662 196874402 87500766 180155696 277166688 6929.065 23985.295 20
. . . . . . . 16
1276799 7811997 3564399 4044377.5 11290560 2751.277 16833.479 12
. . . . . . . 11
end
label values isic1 isic1
label def isic1 1 "15", modify
label def isic1 2 "16", modify
label def isic1 3 "17", modify
label def isic1 4 "18", modify
label def isic1 5 "19", modify
label def isic1 6 "20", modify
label def isic1 7 "21", modify
label def isic1 8 "22", modify
label def isic1 9 "23", modify
label def isic1 10 "24", modify
label def isic1 11 "25", modify
label def isic1 12 "26", modify
label def isic1 13 "27", modify
label def isic1 14 "28", modify
label def isic1 15 "29", modify
label def isic1 16 "30", modify
label def isic1 17 "31", modify
label def isic1 18 "32", modify
label def isic1 19 "33", modify
label def isic1 20 "34", modify
label def isic1 21 "35", modify
label def isic1 22 "36", modify
label def isic1 23 "37", modify
label def isic1 24 "D", modify
[/*CODE]


Thanks!

Hugo

[Hugo Rocha]

     input double(Wages Output ValueAdded) float(realtotalwages realtotalvalueadded realwageperworker realoutputperworker) long isic1
         347826     2017391           .   1101770.6           .         .         . 11
         5882798     8063997     6719997    18634300    21286206  2602.556  3567.521 20
        1418254    26418463     5422737     4492449    17177016  10375.17 193263.05  9
       310799     2018800      828801    984484.3     2625303 2316.4336 15046.432 11
         .           .           .           .           .         .         . 23
       602000     2492000     1190000     1906890     3769434 2011.4873  8326.622 22
      4639286    42526787    19330358    14695363    61230676  9184.602  84192.18  2
      589005     1832461     1361257   1865727.3     4311906 1680.8353  5229.269  8
         .           .           .           .           .         .         . 18
      30313953   190625807    87498908    96022216   277160800  7060.457  44398.88 10
      7000       84000       42000    22173.14   133038.84  739.1047  8869.256 19
  13074957    66439523    29812606    41416120    94434152 2436.2424 12379.604  8
      7.400e+09   3.341e+10   1.439e+10 23440177152 45581639680 21906.707  98905.82 13
         .           .           .           .           .         .         . 23
         .           .           .           .           .         .         . 19
         .           .           .           .           .         .         . 18
 168000168  1447951448   443100443   532155904  1403561216 1673.4463 14423.015 13
    353208     7396226     4996226   1118818.6    15826003  4885.671 102306.65  2
   6923077    41538462    19230769    21929480    60915220 1423.9922  8543.953  8
         .           .           .           .           .         .         . 18
         .           .           .           .           .         .         . 21
         .           .           .           .           .         .         . 11
         .           .           .           .           .         .         .  5
 458964835  1761683205   815937484  1453813120  2584556544  14114.69   54177.6 17
 104938272   727777778           .   332401568           .  3462.516  24013.57 10
   8183258   101523544    38103295    25921218   120695672  2254.019 27963.924 20
  27999989    97999961    54599978    88692528   172950416  7712.394 26993.377 12
         .           .           .           .           .         .         . 14
         .           .           .           .           .         .         . 16
         .  4427499355           .           .           .         . 72816.766  1
         .           .           .           .           .         .         .  5
    423077     3076923     1230769     1340135     3898573  822.1688  5979.408 19
   3846154    51538462    11538462    12183045    36549132  895.8121  12003.88  9
         .           .           .           .           .         .         . 11
         .           .           .           .           .         .         . 16
    522190     3868963     1384012   1654084.6   4383984.5 1848.1393 13693.066 14
         .           .           .           .           .         .         . 16
         .           .           .           .           .         .         . 18
 258833490   976183066   425267870   819878720  1347074816 11277.562     42533 20
  10000000    61066667    25333333    31675914    80245648  5027.923  30703.85  6
 217333333   1.329e+09   5.840e+08   688423232  1849873408    4469.7 27332.354 24
         .           .           .           .           .         .         . 18
         .           .           .           .           .         .         . 16
   3940494    18825329     8746880    12481875    27706542 1358.6454  6490.797 22
    781200    10259196     3401999   2474522.5    10776143 4063.2554  53361.15  9
         .           .           .           .           .         .         .  4
         .           .           .           .           .         .         . 23
 109796432   409803584   187504470   347790240   593937536    8013.6 29909.916  3
         .  2347348701  1178231582           .  3732156160         .  19764.81 17
     34783      556522           .   110178.34           .         .         . 13
  86520087   509670510   171990172   274060288   544794624 1323.9628  7799.168 15
1052799579  6299997480  2911998835  3334839040  9224023040  7510.899  44945.54 10
  17611940    65671642    38805970    55787432   122921456  7621.234  28418.16  8
         .           .           .           .           .         .         . 12
         0           0           0           0           0         .         . 20
    417391     1947826           .     1322124           .         .         .  4
 3.210e+09   1.233e+10   7.050e+09 10167968768 22331518976  17714.23 68042.516 12
    375200     1402799      604800   1188480.3   1915759.3 1342.9155  5020.897  8
    739200     6271997     2631999   2341483.5     8337098  3121.978   26489.5 10
     75600     1204001      358400    239469.9   1135264.8 2238.0366 35642.836 10
    246400     2038400      671999    780494.6   2128618.3  2001.268 16555.945  3
         .           .           .           .           .         .         . 16
         0           0           0           0           0         .         . 17
 727777778  5852777778  2252777778  2305302784  7135879680  3641.869  29287.85 20
   4483504    14703795     9331569    14201909    29558598 1482.7635 4862.7705  8
         .           .           .           .           .         .         . 22
   1005200     2609599     1251599     3184063     3964554  646.7729 1679.0867  6
         .           .           .           .           .         .         . 16
         .           .           .           .           .         .         . 14
         .           .           .           .           .         .         . 22
    259000     1000000      310000    820406.2    981953.4 1827.1853   7054.77 14
         .           .           .           .           .         .         .  8
    891000     4740000     2020000     2822324     6398535   5588.76  29731.45 14
  38000000           .   184266667   120368472   583681536 1744.4706         . 13
8261108333 65227750000 24744441667 26167816192 78380277760  2704.684  21355.54 24
         .           .           .           .           .         .         . 21
         .           .           .           .           .         .         . 23
   4441504    32319872     9650980    14068870    30570362  988.3295  7191.862 13
   3068722    25061227    10229072     9720458    32401520  1905.972 15565.437 13
         .           .           .           .           .         .         .  9
   7423151   104884165    16038156    23513510    50802324  6877.306  97171.74  9
  21093057   132092314    53532148    66814188   169567968  9572.233  59944.77  7
         .           .           .           .           .         .         .  1
 968888889           .           .  3069044224           . 3671.1055         .  6
   2450004    16006690     6533343     7760612    20694962  3767.287  24612.94 20
         .           .           .           .           .         .         .  5
   4347356    45263645    15855062    13770648    50222360 2118.5613  22057.96  7
  14328358   107164179    53731343    45386384   170198944  7416.076  55466.07 11
     69565      417391           .    220353.5           .         .         .  7
         .           .           .           .           .         .         . 16
  45022500           .           .   142612880           .  1821.365         . 15
         .           .           .           .           .         .         . 23
 158563668   707182270   256905771   502264928   813772544  4367.521 19478.822  4
         .           .           .           .           .         .         . 13
    392000     2455599      859600   1241695.9   2722861.5 1069.5055  6699.685  3
   1121191    12943783    10796321     3551475    34198336 2219.6719 25625.385  9
  56874662   196874402    87500766   180155696   277166688  6929.065 23985.295 20
         .           .           .           .           .         .         . 16
   1276799     7811997     3564399   4044377.5    11290560  2751.277 16833.479 12
         .           .           .           .           .         .         . 11
end
label values isic1 isic1
label def isic1 1 "15", modify
label def isic1 2 "16", modify
label def isic1 3 "17", modify
label def isic1 4 "18", modify
label def isic1 5 "19", modify
label def isic1 6 "20", modify
label def isic1 7 "21", modify
label def isic1 8 "22", modify
label def isic1 9 "23", modify
label def isic1 10 "24", modify
label def isic1 11 "25", modify
label def isic1 12 "26", modify
label def isic1 13 "27", modify
label def isic1 14 "28", modify
label def isic1 15 "29", modify
label def isic1 16 "30", modify
label def isic1 17 "31", modify
label def isic1 18 "32", modify
label def isic1 19 "33", modify
label def isic1 20 "34", modify
label def isic1 21 "35", modify
label def isic1 22 "36", modify
label def isic1 23 "37", modify
label def isic1 24 "D", modify

------------------ copy up to and including the previous line ------------------

Listed 100 out of 240448 observations
Use the count() option to list more

[Clyde Schechter]

Cannot be done because you do not have a year variable. You could do it for each industry:

by isic1, sort: egen highest_output_per_worker = max(realwageperworker)

If you had a year variable, you would just change -by isic1- to -by isic1 year-.

[Hugo Rocha]

Thanks! I have a year variable though I do not understand why it did not show up in dataex

[Hugo Rocha]

I know this is unrelated to the post. But if I wanted for every cell to indicate instead of the highest output per worker in sector i in year t, the real output per worker for a specific country in sector i in year t? Thanks!

[Clyde Schechter]

Let's assume you have a country variable, and that it is a string. And let's assume that the country you want to focus on is China. Then it would be:

isid sector year country, sort
by sector year: egen china_output = max(cond(country == "China", realoutputperworker, .))

Note that, unlike the earlier problem, in this case the use of -max()- is not to find the maximum value of realoutputperworker. There will, in every sector and year, be only one observation that satisfies the condition -country == "China"-. So, while it is commonest to use -max()- to single that one out, we could also use -min()- or -mean()- or -median()- or -mode()-: they will all be the same. I do not know why -max()- is the function most commonly used in this context. Perhaps it is the least computationally intensive of the alternatives.

[Nick Cox]

Clyde Schechter is bang on -- and I often use similar constructs. Here's another way to do it:

 
 by sector year: gen china_output = realoutputperworker if country == "China"   by sector year (china_output) : replace china_output = china_output[1]   

See also https://www.stata-journal.com/articl...article=dm0055 for various tips and tricks in this territory.

[Hugo Rocha]

Thank you both! Extremely helpful!