Share of employment, sales and capital using a dummy as a criterion for subset of variables

[Hugo Rocha]

Dear all,

I have a set of variables (by country and firm_id) and I would like to obtain as variables the share of employment, sales and capital (k) by year (y1,y2, and y3) owned by foreigners (d_foreigny1, d_foreigny2, and dforeign_y3) across different technological intensities (1,2, and 3). For instance, I would like to know what's the share of employment in low tech industries (tech_intensity==1) in year 2 owned by foreigners (dforeign_y2)? Is there a quick way of doing a loop? Thank you very much!

 * Example generated by -dataex-. To install: ssc install dataex
clear
input str3 countryc double(firm_id sales_us_y1 sales_us_y2 sales_us_y3) byte(dforeign_y1 dforeign_y2 dforeign_y3) float(tech_intensity k_us_y1 k_us_y2 k_us_y3)
"COL" 144164         . 895030.13         . . . . 1         .         .         .
"COL" 984632         . 741413.31         . . . . 1         .         .         .
"COL" 144942         .         .         . . . . 1         .         .         .
"COL" 146091         .         .         . . . . 1         .         .         .
"COL" 986206         .  96506720         . . . . 1         .         .         .
"COL" 142867         . 1756593.3         . . . . 1         .         .         .
"CHL" 203933 191349424 179680864         . . 0 . 1         .         .         .
"CHL" 200353   7558187   9769028         . . 0 . 1         .         .         .
"ARG" 377606 1764941.3 1891374.5 2102045.8 . . 0 1         0         0         0
"COL" 217632         . 341589.06         . . . . 1         .         .         .
"COL" 145891         .         .         . . . . 1         .         .         .
"COL" 984792         .         .         . . . . 1         .         .         .
"COL" 144784         . 170655.41         . . . . 1         .         .         .
"COL" 147180         .         .         . . . . 1         .         .         .
"COL" 142598         . 1355246.1         . . . . 1         .         .         .
"COL" 978624         .         .         . . . . 1         .         .         .
"COL" 866510         .  113195.7         . . . . 1         .         .         .
"COL" 866435         . 277402.28         . . . . 1         .         .         .
"COL" 866075         . 172340.03         . . . . 1         .         .         .
"COL" 985964         .         .         . . . . 1         .         .         .
"COL" 146215         .         .         . . . . 1         .         .         .
"COL" 984873         .         .         . . . . 1         .         .         .
"COL" 144163         . 3261192.3         . . . . 1         .         .         .
"COL" 981371         . 338917.81         . . . . 1         .         .         .
"COL" 982174         . 255162032         . . . . 1         .         .         .
"COL" 982666         .         .         . . . . 1         .         .         .
"COL" 980671         .         .         . . . . 1         .         .         .
"ARG" 412230 4229750.5   5408857   4235947 . . 0 1         0         0         0
"COL" 986356         . 119001696         . . . . 1         .         .         .
"COL" 982253         .         .         . . . . 1         .         .         .
"ARG" 822099 387336.69 417528.91 629631.31 . . 0 1         0         0         0
"COL" 978311         .         .         . . . . 1         .         .         .
"COL" 866859         .         .         . . . . 1         .         .         .
"CHL" 203706 1146376.4 1260787.6         . . 0 . 1         .         .         .
"COL" 980131         .         .         . . . . 1         .         .         .
"COL" 367060         .         .         . . . . 1         .         .         .
"COL" 866127         . 415713.56         . . . . 1         .         .         .
"COL" 217307         .   1425589         . . . . 1         .         .         .
"ARG" 379950 1838233.3   1762104 2117483.8 . . 0 1         0         0         0
"COL" 142469         . 1039074.7         . . . . 1         .         .         .
"COL" 146451         . 259426.83         . . . . 1         .         .         .
"COL" 367526         .  68095080         . . . . 1         .         .         .
"COL" 427268         .         .         . . . . 1         .         .         .
"COL" 147320         .         .         . . . . 1         .         .         .
"COL" 147153         .         .         . . . . 1         .         .         .
"COL" 217297         .         .         . . . . 1         .         .         .
"CHL" 201442 6011652.5 6033862.5         . . 0 . 1         .         .         .
"COL" 145569         .         .         . . . . 1         .         .         .
"COL" 866013         .         .         . . . . 1         .         .         .
"ARG" 325536 1491505.1 1643639.4 1408750.4 . . 0 1         0         0         0
"COL" 973577         .         .         . . . . 1         .         .         .
"ARG" 482950 6029462.5   6068866   4433717 . . 0 1   64617.6 38104.832  42023.15
"ARG" 771900 1884529.8   2893023 2211637.8 . . 0 1         0         0         0
"ARG" 229269   4720811 5264995.5 2943076.3 . . 0 1         0         0  9918.592
"COL" 981343         . 340790.47         . . . . 1         .         .         .
"COL" 146415         . 264110.81         . . . . 1         .         .         .
"COL" 982239         . 998287.13         . . . . 1         .         .         .
"COL" 986472         .  45457068         . . . . 1         .         .         .
"ARG" 629313 331374.03 402632.09 476932.63 . . 0 1         0         0         0
"COL" 147480         .         .         . . . . 1         .         .         .
"COL" 145608         . 648903.19         . . . . 1         .         .         .
"COL" 980109         .   7235772         . . . . 1         .         .         .
"COL" 980171         .         .         . . . . 1         .         .         .
"COL" 367500         . 1654624.1         . . . . 1         .         .         .
"COL" 367119         .         .         . . . . 1         .         .         .
"COL" 147341         . 2913357.3         . . . . 1         .         .         .
"COL" 147623         .         .         . . . . 1         .         .         .
"COL" 977415         .   6814513         . . . . 1         .         .         .
"COL" 866323         .         .         . . . . 1         .         .         .
"COL" 985225         .         .         . . . . 1         .         .         .
"COL" 980854         . 2811370.5         . . . . 1         .         .         .
"COL" 983761         .         .         . . . . 1         .         .         .
"ARG" 106127 1864263.4 2620682.8 2456369.8 . . 0 1  16313.96  4641.935 4100.5664
"COL" 366560         .         .         . . . . 1         .         .         .
"COL" 142201         . 372053.19         . . . . 1         .         .         .
"COL" 146213         .         .         . . . . 1         .         .         .
"COL" 980615         .   6724162         . . . . 1         .         .         .
"COL" 147016         .         .         . . . . 1         .         .         .
"COL" 982331         .         .         . . . . 1         .         .         .
"CHL" 202089  10682692   7954331         . . 0 . 1         .         .         .
"ARG" 874560   1911212 2378686.8 2844000.3 . . 0 1         0         0         0
"COL" 866048         .         .         . . . . 1         .         .         .
"COL" 143187         .  136326.2         . . . . 1         .         .         .
"COL" 866578         .         .         . . . . 1         .         .         .
"COL" 980764         .  16858244         . . . . 1         .         .         .
"COL" 865907         .         .         . . . . 1         .         .         .
"COL" 981743         .         .         . . . . 1         .         .         .
"COL" 865431         .  46099128         . . . . 1         .         .         .
"COL" 865863         .         .         . . . . 1         .         .         .
"COL" 865971         .         .         . . . . 1         .         .         .
"COL" 986338         .         .         . . . . 1         .         .         .
"COL" 217216         . 556180.25         . . . . 1         .         .         .
"COL" 145378         . 1163224.1         . . . . 1         .         .         .
"ARG" 493190    346483 476869.47  511358.5 . . 0 1         .         .         .
"COL" 367481         . 136525.44         . . . . 1         .         .         .
"ARG" 540958 3102608.5 4232600.5   6055020 . . 0 1         0         0         0
"ARG" 226611         .         .         . . . 0 1         .         .         .
"ARG" 670987  56999020 123840080 125635496 . . 1 1 2434706.3   6024676   9947744
"COL" 865938         .         .         . . . . 1         .         .         .
"COL" 865598         .         .         . . . . 1         .         .         .
end

Employment

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(empl_y1 empl_y2 empl_y3)
  23   27   .
  21   16   .
  26   26   .
  70  105   .
 535  641   .
  40   45   .
 288  296   .
  45   47   .
 110  110 115
  10   11   .
  11    8   .
  20   16   .
   9    9   .
  37   28   .
  30   30   .
 487  458   .
   8   14   .
  11    9   .
  10   11   .
2009 1698   .
  17   18   .
 216  161   .
   0   62   .
   9    9   .
 618  640   .
 146  134   .
 172  169   .
   8   10  10
 328  340   .
   4    5   .
  15   12  12
 299  279   .
  32   41   .
  31   31   .
  24    7   .
  11   11   .
  33   30   .
  16   18   .
  37   35  38
  40   39   .
   6    6   .
 847  770   .
  24   24   .
  57   63   .
 249  235   .
   3    2   .
  77   87   .
  44   45   .
  12   12   .
  13   11  11
 773  709   .
  80   82  68
  15   16  17
 104   99  81
  16   15   .
  27   22   .
  19   21   .
 101  102   .
  23   22  18
   9    9   .
  14   12   .
  63   64   .
  25   25   .
  34   36   .
   6    6   .
  31   31   .
  50   40   .
 136   92   .
   4    5   .
  66   48   .
 119  117   .
   6    4   .
  30   28  28
  13   13   .
  12   13   .
   9   10   .
 110  111   .
  16   18   .
  28   38   .
  40   37   .
  27   27  33
  14   13   .
  15    8   .
   8    8   .
 312  257   .
   9    7   .
  12   11   .
 296  313   .
 230  189   .
   7    7   .
 721  707   .
  37   37   .
  28   28   .
  39   41  39
   2    2   .
  96   96 103
  21   21  21
   0  400 400
   5    6   .
   8    9   .
end

[Nick Cox]

Here's a guess at code for employment

forval y = 1/3 { 
       egen total_empl`y' = total(empl_yr`j'), by(intensity) 
       egen foreign_empl`y' = total(empl_yr`y' * dforeign_y`y'), by(intensity) 
       gen pr_foreign_empl`y' = foreign_empl`y' /total_empl`y' 
} 
 
tabdisp intensity, c(pr_foreign_empl?)

[Hugo Rocha]

Thank you very much! I tried something similar before but I get is that, in your code, total_empl`y' is ambiguous. I know the `y' ideally makes for all three cases in the data (empl_y1, empl_y2, and empl_y3)...

For

 egen foreign_empl`y' = total(empl_yr`y' * dforeign_y`y'), by(intensity) (But doing it manually per y1, y2 and y3)

I get zeros because dforeign is a dummy...

[Hugo Rocha]

My goal is to regress log of sales per employee per type of industry on the share of physical capital, employment, and sales of foreign firms in that specific industry

[Nick Cox]

Multiplying by an indicator (you say dummy) seems vital here. Otherwise sorry about some typos. Try this.

clear
input str3 countryc double(firm_id sales_us_y1 sales_us_y2 sales_us_y3) byte(dforeign_y1 dforeign_y2 dforeign_y3) float(tech_intensity k_us_y1 k_us_y2 k_us_y3)
"COL" 144164         . 895030.13         . . . . 1         .         .         .
"COL" 984632         . 741413.31         . . . . 1         .         .         .
"COL" 144942         .         .         . . . . 1         .         .         .
"COL" 146091         .         .         . . . . 1         .         .         .
"COL" 986206         .  96506720         . . . . 1         .         .         .
"COL" 142867         . 1756593.3         . . . . 1         .         .         .
"CHL" 203933 191349424 179680864         . . 0 . 1         .         .         .
"CHL" 200353   7558187   9769028         . . 0 . 1         .         .         .
"ARG" 377606 1764941.3 1891374.5 2102045.8 . . 0 1         0         0         0
"COL" 217632         . 341589.06         . . . . 1         .         .         .
"COL" 145891         .         .         . . . . 1         .         .         .
"COL" 984792         .         .         . . . . 1         .         .         .
"COL" 144784         . 170655.41         . . . . 1         .         .         .
"COL" 147180         .         .         . . . . 1         .         .         .
"COL" 142598         . 1355246.1         . . . . 1         .         .         .
"COL" 978624         .         .         . . . . 1         .         .         .
"COL" 866510         .  113195.7         . . . . 1         .         .         .
"COL" 866435         . 277402.28         . . . . 1         .         .         .
"COL" 866075         . 172340.03         . . . . 1         .         .         .
"COL" 985964         .         .         . . . . 1         .         .         .
"COL" 146215         .         .         . . . . 1         .         .         .
"COL" 984873         .         .         . . . . 1         .         .         .
"COL" 144163         . 3261192.3         . . . . 1         .         .         .
"COL" 981371         . 338917.81         . . . . 1         .         .         .
"COL" 982174         . 255162032         . . . . 1         .         .         .
"COL" 982666         .         .         . . . . 1         .         .         .
"COL" 980671         .         .         . . . . 1         .         .         .
"ARG" 412230 4229750.5   5408857   4235947 . . 0 1         0         0         0
"COL" 986356         . 119001696         . . . . 1         .         .         .
"COL" 982253         .         .         . . . . 1         .         .         .
"ARG" 822099 387336.69 417528.91 629631.31 . . 0 1         0         0         0
"COL" 978311         .         .         . . . . 1         .         .         .
"COL" 866859         .         .         . . . . 1         .         .         .
"CHL" 203706 1146376.4 1260787.6         . . 0 . 1         .         .         .
"COL" 980131         .         .         . . . . 1         .         .         .
"COL" 367060         .         .         . . . . 1         .         .         .
"COL" 866127         . 415713.56         . . . . 1         .         .         .
"COL" 217307         .   1425589         . . . . 1         .         .         .
"ARG" 379950 1838233.3   1762104 2117483.8 . . 0 1         0         0         0
"COL" 142469         . 1039074.7         . . . . 1         .         .         .
"COL" 146451         . 259426.83         . . . . 1         .         .         .
"COL" 367526         .  68095080         . . . . 1         .         .         .
"COL" 427268         .         .         . . . . 1         .         .         .
"COL" 147320         .         .         . . . . 1         .         .         .
"COL" 147153         .         .         . . . . 1         .         .         .
"COL" 217297         .         .         . . . . 1         .         .         .
"CHL" 201442 6011652.5 6033862.5         . . 0 . 1         .         .         .
"COL" 145569         .         .         . . . . 1         .         .         .
"COL" 866013         .         .         . . . . 1         .         .         .
"ARG" 325536 1491505.1 1643639.4 1408750.4 . . 0 1         0         0         0
"COL" 973577         .         .         . . . . 1         .         .         .
"ARG" 482950 6029462.5   6068866   4433717 . . 0 1   64617.6 38104.832  42023.15
"ARG" 771900 1884529.8   2893023 2211637.8 . . 0 1         0         0         0
"ARG" 229269   4720811 5264995.5 2943076.3 . . 0 1         0         0  9918.592
"COL" 981343         . 340790.47         . . . . 1         .         .         .
"COL" 146415         . 264110.81         . . . . 1         .         .         .
"COL" 982239         . 998287.13         . . . . 1         .         .         .
"COL" 986472         .  45457068         . . . . 1         .         .         .
"ARG" 629313 331374.03 402632.09 476932.63 . . 0 1         0         0         0
"COL" 147480         .         .         . . . . 1         .         .         .
"COL" 145608         . 648903.19         . . . . 1         .         .         .
"COL" 980109         .   7235772         . . . . 1         .         .         .
"COL" 980171         .         .         . . . . 1         .         .         .
"COL" 367500         . 1654624.1         . . . . 1         .         .         .
"COL" 367119         .         .         . . . . 1         .         .         .
"COL" 147341         . 2913357.3         . . . . 1         .         .         .
"COL" 147623         .         .         . . . . 1         .         .         .
"COL" 977415         .   6814513         . . . . 1         .         .         .
"COL" 866323         .         .         . . . . 1         .         .         .
"COL" 985225         .         .         . . . . 1         .         .         .
"COL" 980854         . 2811370.5         . . . . 1         .         .         .
"COL" 983761         .         .         . . . . 1         .         .         .
"ARG" 106127 1864263.4 2620682.8 2456369.8 . . 0 1  16313.96  4641.935 4100.5664
"COL" 366560         .         .         . . . . 1         .         .         .
"COL" 142201         . 372053.19         . . . . 1         .         .         .
"COL" 146213         .         .         . . . . 1         .         .         .
"COL" 980615         .   6724162         . . . . 1         .         .         .
"COL" 147016         .         .         . . . . 1         .         .         .
"COL" 982331         .         .         . . . . 1         .         .         .
"CHL" 202089  10682692   7954331         . . 0 . 1         .         .         .
"ARG" 874560   1911212 2378686.8 2844000.3 . . 0 1         0         0         0
"COL" 866048         .         .         . . . . 1         .         .         .
"COL" 143187         .  136326.2         . . . . 1         .         .         .
"COL" 866578         .         .         . . . . 1         .         .         .
"COL" 980764         .  16858244         . . . . 1         .         .         .
"COL" 865907         .         .         . . . . 1         .         .         .
"COL" 981743         .         .         . . . . 1         .         .         .
"COL" 865431         .  46099128         . . . . 1         .         .         .
"COL" 865863         .         .         . . . . 1         .         .         .
"COL" 865971         .         .         . . . . 1         .         .         .
"COL" 986338         .         .         . . . . 1         .         .         .
"COL" 217216         . 556180.25         . . . . 1         .         .         .
"COL" 145378         . 1163224.1         . . . . 1         .         .         .
"ARG" 493190    346483 476869.47  511358.5 . . 0 1         .         .         .
"COL" 367481         . 136525.44         . . . . 1         .         .         .
"ARG" 540958 3102608.5 4232600.5   6055020 . . 0 1         0         0         0
"ARG" 226611         .         .         . . . 0 1         .         .         .
"ARG" 670987  56999020 123840080 125635496 . . 1 1 2434706.3   6024676   9947744
"COL" 865938         .         .         . . . . 1         .         .         .
"COL" 865598         .         .         . . . . 1         .         .         .
end

input float(empl_y1 empl_y2 empl_y3)
  23   27   .
  21   16   .
  26   26   .
  70  105   .
 535  641   .
  40   45   .
 288  296   .
  45   47   .
 110  110 115
  10   11   .
  11    8   .
  20   16   .
   9    9   .
  37   28   .
  30   30   .
 487  458   .
   8   14   .
  11    9   .
  10   11   .
2009 1698   .
  17   18   .
 216  161   .
   0   62   .
   9    9   .
 618  640   .
 146  134   .
 172  169   .
   8   10  10
 328  340   .
   4    5   .
  15   12  12
 299  279   .
  32   41   .
  31   31   .
  24    7   .
  11   11   .
  33   30   .
  16   18   .
  37   35  38
  40   39   .
   6    6   .
 847  770   .
  24   24   .
  57   63   .
 249  235   .
   3    2   .
  77   87   .
  44   45   .
  12   12   .
  13   11  11
 773  709   .
  80   82  68
  15   16  17
 104   99  81
  16   15   .
  27   22   .
  19   21   .
 101  102   .
  23   22  18
   9    9   .
  14   12   .
  63   64   .
  25   25   .
  34   36   .
   6    6   .
  31   31   .
  50   40   .
 136   92   .
   4    5   .
  66   48   .
 119  117   .
   6    4   .
  30   28  28
  13   13   .
  12   13   .
   9   10   .
 110  111   .
  16   18   .
  28   38   .
  40   37   .
  27   27  33
  14   13   .
  15    8   .
   8    8   .
 312  257   .
   9    7   .
  12   11   .
 296  313   .
 230  189   .
   7    7   .
 721  707   .
  37   37   .
  28   28   .
  39   41  39
   2    2   .
  96   96 103
  21   21  21
   0  400 400
   5    6   .
   8    9   .

forval y = 1/3 { 
       egen total_empl`y' = total(empl_y`y'), by(tech_intensity) 
       egen total_sales`y' = total(sales_us_y`y'), by(tech_intensity)
       egen total_k`y' = total(k_us_y`y'), by(tech_intensity) 
       
       egen foreign_empl`y' = total(empl_y`y' * dforeign_y`y'), by(tech_intensity) 
       gen pr_foreign_empl`y' = foreign_empl`y' /total_empl`y' 
       
       egen foreign_sales`y' = total(sales_us_y`y' * dforeign_y`y'), by(tech_intensity) 
       gen pr_foreign_sales`y' = foreign_sales`y' /total_sales`y' 
       
       egen foreign_k`y' = total(k_us_y`y' * dforeign_y`y'), by(tech_intensity) 
       gen pr_foreign_k`y' = foreign_k`y' /total_k`y' 
} 

foreach x in empl sales k { 
    tabdisp tech_intensity, c(pr_foreign_`x'*)
}

[Hugo Rocha]

Thank you very much, I am trying it right now!

[Hugo Rocha]

Thank you! The loop worked fantastically, I am going to change it a bit to use more industries!