Reference Country (for industries)

[Hugo Rocha]

Dear all,

Thank you so much for all your help!
In the data below, I want to compare the value added per worker for a set of three industries (tech_intensity) using the US (country1==840) as a reference country. For instance, I want obtain the ratio of value added per worker( r_valworker) in industry (tech_intensity==1) for country (Bolivia==68) over the ratio of value added per worker (r_valworker) in industry (tech_intensity==1) for country US (country1==840). How can I get this variable for all set of countries using these industries' classification as reference and hence obtain three variables for the types of industries? Ideally, I would like to get these variables using the US as a country of reference (for the denominators). Thank you so much again!

 * Example generated by -dataex-. To install: ssc install dataex
clear
input int(country1 year) float tech_intensity double(Establishments Employment Wages OutputINDSTAT4 ValueAdded GrossFixed) float(r_valworker r_output_worker lval_per_worker share_emp_high share_emp_low)
4 1973 3                  .               51.8                  .                  . . . .         . . .015424144  .8860162
4 1973 2                  .                331                  .                  . . . .         . . .015424144  .8860162
4 1973 1                  . 2975.5714285714284                  .                  . . . .         . . .015424144  .8860162
4 1974 2                  .              332.4                  .                  . . . .         . .   .1526138  .7700793
4 1974 1                  . 3311.1428571428573                  .                  . . . .         . .   .1526138  .7700793
4 1974 3                  .              656.2                  .                  . . . .         . .   .1526138  .7700793
4 1975 2                  .              430.6                  .                  . . . .         . .    .158998  .7563943
4 1975 3                  .              809.2                  .                  . . . .         . .    .158998  .7563943
4 1975 1                  . 3849.5714285714284                  .                  . . . .         . .    .158998  .7563943
4 1976 2                  .              383.6                  .                  . . . .         . .  .16229185  .7682115
4 1976 1                  .  4240.285714285715                  .                  . . . .         . .  .16229185  .7682115
4 1976 3                  .              895.8                  .                  . . . .         . .  .16229185  .7682115
4 1977 1                  .  4536.571428571428                  .                  . . . .         . .   .1601448   .770586
4 1977 3                  .              942.8                  .                  . . . .         . .   .1601448   .770586
4 1977 2                  .              407.8                  .                  . . . .         . .   .1601448   .770586
4 1978 1                  .  4949.857142857143                  .                  . . . .         . .  .15292776   .768342
4 1978 3                  .              985.2                  .                  . . . .         . .  .15292776   .768342
4 1978 2                  .              507.2                  .                  . . . .         . .  .15292776   .768342
4 1979 3                  .              875.4                  .                  . . . .         . .  .13531454  .7813079
4 1979 1                  .  5054.571428571428                  .                  . . . .         . .  .13531454  .7813079
4 1979 2                  .              539.4                  .                  . . . .         . .  .13531454  .7813079
4 1980 2                  .                515                  .                  . . . .         . .  .14345832  .7692531
4 1980 1                  .  4538.571428571428                  .                  . . . .         . .  .14345832  .7692531
4 1980 3                  .              846.4                  .                  . . . .         . .  .14345832  .7692531
4 1981 3                  1                571                  .                  . . . .         . .  .10808428  .8097262
4 1981 2                8.2              434.2                  .                  . . . .         . .  .10808428  .8097262
4 1981 1 23.571428571428573  4277.714285714285                  .                  . . . .         . .  .10808428  .8097262
4 1982 3                  1              535.6                  .                  . . . .         . .  .11449337  .7896537
4 1982 2                7.4              448.4                  .                  . . . .         . .  .11449337  .7896537
4 1982 1 25.285714285714285               3694                  .                  . . . .         . .  .11449337  .7896537
4 1983 3                1.8              930.2                  .                  . . . .         . .  .21483813  .7188766
4 1983 1 24.285714285714285 3112.5714285714284                  .                  . . . .         . .  .21483813  .7188766
4 1983 2                  8                287                  .                  . . . .         . .  .21483813  .7188766
4 1984 2                  8              270.8                  .                  . . . .         . .  .20192805  .7342654
4 1984 3                1.8                857                  .                  . . . .         . .  .20192805  .7342654
4 1984 1 28.285714285714285  3116.285714285714                  .                  . . . .         . .  .20192805  .7342654
4 1985 1                 32 3275.5714285714284                  .                  . . . .         . .   .2065464   .732205
4 1985 3                1.8                924                  .                  . . . .         . .   .2065464   .732205
4 1985 2                8.8                274                  .                  . . . .         . .   .2065464   .732205
4 1986 1 39.285714285714285  4101.285714285715                  .                  . . . .         . .   .1600479  .7849823
4 1986 2                9.4              287.2                  .                  . . . .         . .   .1600479  .7849823
4 1986 3                2.2              836.2                  .                  . . . .         . .   .1600479  .7849823
4 1987 3                2.2              936.8                  .                  . . . .         . .   .1578889  .7862904
4 1987 1 44.285714285714285  4665.285714285715                  .                  . . . .         . .   .1578889  .7862904
4 1987 2               13.6              331.2                  .                  . . . .         . .   .1578889  .7862904
4 1988 3                2.4              990.4                  .                  . . . .         . .   .1737091  .7649385
4 1988 1 48.142857142857146  4361.285714285715                  .                  . . . .         . .   .1737091  .7649385
4 1988 2               14.8              349.8                  .                  . . . .         . .   .1737091  .7649385
4 1990 2                  .                  .                  .                  . . . .         . .          .         .
4 1990 3                  .                  .                  .                  . . . .         . .          .         .
4 1990 1                  .                  .                  .                  . . . .         . .          .         .
4 1991 3                  .                  .                  .                  . . . .         . .          .         .
4 1991 2                  .                  .                  .                  . . . .         . .          .         .
4 1991 1                  .                  .                  .                  . . . .         . .          .         .
4 1998 2                  .                  .                  .                  . . . .         . .          .         .
4 1998 3                  .                  .                  .                  . . . .         . .          .         .
4 1998 1                  .                  .                  .                  . . . .         . .          .         .
4 1999 3                  .                  .                  .                  . . . .         . .          .         .
4 1999 1                  .                  .                  .                  . . . .         . .          .         .
4 1999 2                  .                  .                  .                  . . . .         . .          .         .
4 2001 1                  .                  .                  .                  . . . .         . .          .         .
4 2001 2                  .                  .                  .                  . . . .         . .          .         .
4 2001 3                  .                  .                  .                  . . . .         . .          .         .
4 2002 1              28.75             907.75             944394         2671288.25 . . .  1642.744 .  .28250483  .3043843
4 2002 2               50.5               1232 1188568.3333333333           615059.5 . . . 597.68066 .  .28250483  .3043843
4 2002 3                5.5              842.5           905367.5              48240 . . . 26.487877 .  .28250483  .3043843
4 2003 1               33.5             1440.5         1454829.75            1927701 . . .  870.9985 .    .352271 .33841035
4 2003 2 52.666666666666664 1316.6666666666667 1231267.6666666667 1346656.3333333333 . . .  775.0224 .    .352271 .33841035
4 2003 3                 10             1499.5            1396271          1291969.5 . . .  876.4713 .    .352271 .33841035
4 2004 2 59.333333333333336 1446.6666666666667            1451344            2744957 . . . 1279.0132 .  .39170825  .3096508
4 2004 3               19.5             1897.5            1903635          2612586.5 . . . 1367.1324 .  .39170825  .3096508
4 2004 1               32.5               1500          1435877.5            4101760 . . . 1908.3418 .  .39170825  .3096508
4 2005 3                 65               2427          2369591.5           13365095 . . . 2914.0854 .   .3279951  .3963106
4 2005 1              68.25             2932.5         3023117.75        12556925.75 . . .  2521.687 .   .3279951  .3963106
4 2005 2 54.666666666666664               2040            2043644 15732356.333333334 . . . 4576.0645 .   .3279951  .3963106
4 2006 2  73.33333333333333               3050 3427117.6666666665 18293986.333333332 . . . 3503.0034 .   .3696422  .3481529
4 2006 3               85.5               3995          4321052.5           13940819 . . .  1822.082 .   .3696422  .3481529
4 2006 1              87.75            3762.75            3945432        14526693.75 . . .  2161.713 .   .3696422  .3481529
4 2007 2                 80               2490 3677740.3333333335           19694961 . . . 4658.5093 .   .3895239  .3752598
4 2007 3               97.5             4123.5            4753811           15091464 . . .  1886.143 .   .3895239  .3752598
4 2007 1              93.25             3972.5         4579799.25        16877821.25 . . . 2355.5303 .   .3895239  .3752598
4 2008 2                 79 3324.3333333333335 3982411.6666666665           20106290 . . .  3194.276 .   .3842091  .3377979
4 2008 1                 96             4039.5         4888196.75         16179228.5 . . . 2050.6348 .   .3842091  .3377979
4 2008 3                105             4594.5            5595744           22534501 . . .  3087.225 .   .3842091  .3377979
4 2009 1              91.25            4022.25         5083234.25         16283159.5 . . . 2346.4924 .    .384414  .3370483
4 2009 2                 81               3324            4431416 20853783.666666668 . . .  3622.996 .    .384414  .3370483
4 2009 3              101.5             4587.5          5791346.5         23306507.5 . . . 3562.5386 .    .384414  .3370483
4 2010 3                104             5192.5          7377542.5         25914665.5 . . .  3501.906 .   .4206014  .3123629
4 2010 1              90.75            3856.25          5478988.5         18891894.5 . . . 2730.8606 .   .4206014  .3123629
4 2010 2  78.33333333333333 3296.6666666666665  5135056.333333333 24767973.666666668 . . . 4130.6343 .   .4206014  .3123629
4 2011 3                102               5197          7604208.5           26116110 . . . 3247.0576 .  .42005575  .3128393
4 2011 1                 92             3870.5         5663292.75         20503237.5 . . .  2590.373 .  .42005575  .3128393
4 2011 2  78.33333333333333 3304.6666666666665            4835347 25112480.666666668 . . . 3987.5266 .  .42005575  .3128393
4 2012 2  76.66666666666667               3080  4246413.666666667           23288179 . . .  3829.427 .   .4237512  .3182113
4 2012 3                100               5058          6972510.5           23956529 . . .  3048.956 .   .4237512  .3182113
4 2012 1               89.5            3798.25         5232574.25         20110405.5 . . .  2418.603 .   .4237512  .3182113
4 2013 2  73.66666666666667 3096.3333333333335 4025702.6666666665 20724421.666666668 . . . 3402.9866 .   .4341611  .2997185
4 2013 3                 98             5051.5            6569455           22849533 . . .  2840.688 .   .4341611  .2997185
4 2013 1              89.75            3487.25            4533881         17272357.5 . . . 2296.0713 .   .4341611  .2997185
4 2014 1                  .                  .                  .           11528111 . . .         . .          .         .
end

[Nick Cox]

Your data example doesn't include any observations for which country1 is 840 but the calculation is standard and can be illustrated with other data. See https://www.stata-journal.com/articl...article=dm0055 especially but not only Section 9.

You don't say so, but I imagine that your calculation should refer to each year separately. Consider this example in which company 5 in the Grunfeld data is somehow regarded as standard.

. webuse grunfeld , clear

. egen ref = total(cond(company == 5, invest, .)), by(year)

. gen ratio = invest / ref

. list invest company ref ratio if year == 1935

     +-------------------------------------+
     | invest   company     ref      ratio |
     |-------------------------------------|
  1. |  317.6         1   39.68   8.004032 |
 21. |  209.9         2   39.68   5.289818 |
 41. |   33.1         3   39.68   .8341733 |
 61. |  40.29         4   39.68   1.015373 |
 81. |  39.68         5   39.68          1 |
     |-------------------------------------|
101. |  20.36         6   39.68   .5131049 |
121. |  24.43         7   39.68   .6156754 |
141. |  12.93         8   39.68   .3258569 |
161. |  26.63         9   39.68   .6711189 |
181. |   2.54        10   39.68   .0640121 |
     +-------------------------------------+

The egen calculation spreads the value for company 5 to all companies, as the total explicitly is for company 5 only.

Then the ratio calculation is immediate.

Another way to spread the reference value would be

egen ref = total((company == 5) * invest)

as the true or false expression company == 5 is true (1) if and only if company is 5 and false (0) otherwise and products that are zero don't affect the total, which is then just a total over one value.

If I am understanding your question correctly the only twist needed is that you also have an industry variable, which just adds another variable to the by() option.

egen ref = total((country1 == 840) *  r_valworker), by(year tech_intensity)

A separate hint is that such ratios are often best analysed on logarithmic scale.

[Hugo Rocha]

Thank you very much! Dataex, I guess, shows only part of the data as below. Also, yes, I am very sorry. I want the ratio for each year relative to the reference country for each year. I've tried the syntax now. Also, my goal is obtaining the ratios and then using on the ration. Thank you very much!

 * Example generated by -dataex-. To install: ssc install dataex
clear
input int(country1 year) float tech_intensity double(Establishments Employment Wages) float(r_valworker lval_per_worker)
4 1973 3                  .               51.8                  . . .
4 1973 2                  .                331                  . . .
4 1973 1                  . 2975.5714285714284                  . . .
4 1974 2                  .              332.4                  . . .
4 1974 1                  . 3311.1428571428573                  . . .
4 1974 3                  .              656.2                  . . .
4 1975 2                  .              430.6                  . . .
4 1975 3                  .              809.2                  . . .
4 1975 1                  . 3849.5714285714284                  . . .
4 1976 2                  .              383.6                  . . .
4 1976 1                  .  4240.285714285715                  . . .
4 1976 3                  .              895.8                  . . .
4 1977 1                  .  4536.571428571428                  . . .
4 1977 3                  .              942.8                  . . .
4 1977 2                  .              407.8                  . . .
4 1978 1                  .  4949.857142857143                  . . .
4 1978 3                  .              985.2                  . . .
4 1978 2                  .              507.2                  . . .
4 1979 3                  .              875.4                  . . .
4 1979 1                  .  5054.571428571428                  . . .
4 1979 2                  .              539.4                  . . .
4 1980 2                  .                515                  . . .
4 1980 1                  .  4538.571428571428                  . . .
4 1980 3                  .              846.4                  . . .
4 1981 3                  1                571                  . . .
4 1981 2                8.2              434.2                  . . .
4 1981 1 23.571428571428573  4277.714285714285                  . . .
4 1982 3                  1              535.6                  . . .
4 1982 2                7.4              448.4                  . . .
4 1982 1 25.285714285714285               3694                  . . .
4 1983 3                1.8              930.2                  . . .
4 1983 1 24.285714285714285 3112.5714285714284                  . . .
4 1983 2                  8                287                  . . .
4 1984 2                  8              270.8                  . . .
4 1984 3                1.8                857                  . . .
4 1984 1 28.285714285714285  3116.285714285714                  . . .
4 1985 1                 32 3275.5714285714284                  . . .
4 1985 3                1.8                924                  . . .
4 1985 2                8.8                274                  . . .
4 1986 1 39.285714285714285  4101.285714285715                  . . .
4 1986 2                9.4              287.2                  . . .
4 1986 3                2.2              836.2                  . . .
4 1987 3                2.2              936.8                  . . .
4 1987 1 44.285714285714285  4665.285714285715                  . . .
4 1987 2               13.6              331.2                  . . .
4 1988 3                2.4              990.4                  . . .
4 1988 1 48.142857142857146  4361.285714285715                  . . .
4 1988 2               14.8              349.8                  . . .
4 1990 2                  .                  .                  . . .
4 1990 3                  .                  .                  . . .
4 1990 1                  .                  .                  . . .
4 1991 3                  .                  .                  . . .
4 1991 2                  .                  .                  . . .
4 1991 1                  .                  .                  . . .
4 1998 2                  .                  .                  . . .
4 1998 3                  .                  .                  . . .
4 1998 1                  .                  .                  . . .
4 1999 3                  .                  .                  . . .
4 1999 1                  .                  .                  . . .
4 1999 2                  .                  .                  . . .
4 2001 1                  .                  .                  . . .
4 2001 2                  .                  .                  . . .
4 2001 3                  .                  .                  . . .
4 2002 1              28.75             907.75             944394 . .
4 2002 2               50.5               1232 1188568.3333333333 . .
4 2002 3                5.5              842.5           905367.5 . .
4 2003 1               33.5             1440.5         1454829.75 . .
4 2003 2 52.666666666666664 1316.6666666666667 1231267.6666666667 . .
4 2003 3                 10             1499.5            1396271 . .
4 2004 2 59.333333333333336 1446.6666666666667            1451344 . .
4 2004 3               19.5             1897.5            1903635 . .
4 2004 1               32.5               1500          1435877.5 . .
4 2005 3                 65               2427          2369591.5 . .
4 2005 1              68.25             2932.5         3023117.75 . .
4 2005 2 54.666666666666664               2040            2043644 . .
4 2006 2  73.33333333333333               3050 3427117.6666666665 . .
4 2006 3               85.5               3995          4321052.5 . .
4 2006 1              87.75            3762.75            3945432 . .
4 2007 2                 80               2490 3677740.3333333335 . .
4 2007 3               97.5             4123.5            4753811 . .
4 2007 1              93.25             3972.5         4579799.25 . .
4 2008 2                 79 3324.3333333333335 3982411.6666666665 . .
4 2008 1                 96             4039.5         4888196.75 . .
4 2008 3                105             4594.5            5595744 . .
4 2009 1              91.25            4022.25         5083234.25 . .
4 2009 2                 81               3324            4431416 . .
4 2009 3              101.5             4587.5          5791346.5 . .
4 2010 3                104             5192.5          7377542.5 . .
4 2010 1              90.75            3856.25          5478988.5 . .
4 2010 2  78.33333333333333 3296.6666666666665  5135056.333333333 . .
4 2011 3                102               5197          7604208.5 . .
4 2011 1                 92             3870.5         5663292.75 . .
4 2011 2  78.33333333333333 3304.6666666666665            4835347 . .
4 2012 2  76.66666666666667               3080  4246413.666666667 . .
4 2012 3                100               5058          6972510.5 . .
4 2012 1               89.5            3798.25         5232574.25 . .
4 2013 2  73.66666666666667 3096.3333333333335 4025702.6666666665 . .
4 2013 3                 98             5051.5            6569455 . .
4 2013 1              89.75            3487.25            4533881 . .
4 2014 1                  .                  .                  . . .
end

  egen ref = total((country1 == 840) *  r_valworker), by(year tech_intensity)

 gen gap= ln(r_valworker/ref)

I am possibly using absolute values given that the ratio in some cases is too low that the log is negative

[Nick Cox]

Negative logarithms are exactly what you get when the argument is below 1.

[Hugo Rocha]

Negative logarithms are exactly what you get when the argument is below 1.

Exactly. And since the the value of value added per worker for country1==840 is too large. I thought about it and will not use logs.