Standard Deviation

[Jon Fredheim]

Hello,

I am fairly new to using Stata, hence I am running into some problems with creating certain variables. I have looked into other topics on this forum, but I could not get the codes provided to work on my dataset. I am for instance trying to create a variable for Standard Deviation of EBIT. The variable need at least 3 years of observation.

Another variable I am trying to create is tax cost of repatriation. This variable is defined as max(o, ((marginal tax rate * pifo) - txfo)) where marginal tax rate is a new variable
- 35% if the company has neither a tax loss carry forward [TLCF] nor negative taxable income [PI]
- 17.5% if either [TLCF] > 0 or [PI] < 0
- 0% if both [TLCF] > 0 and [PI] < 0

I appreciate any comments and help to generate these two variables.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str6 gvkey double(fyear at capx che dlc dltt dp dvt ebit oiadp oibdp pidom pifo seq tlcf txdfo txfo xrd mkvalt) int sic
"001004" 2009  1501.042   28.855     79.37  100.833   336.191    38.93        0   95.415   95.415   134.345        .       .   746.906       0       .      0        .    777.8348 5080
"001004" 2010  1703.727  124.879    57.433  114.075   329.802   59.296    2.983  137.016  137.016   196.312        .       .   835.845       0       .      0        .   1049.8206 5080
"001004" 2011  2195.653   91.218     67.72  122.865   669.489   80.333   12.081   142.36   142.36   222.693        .       .   864.649       0       .  2.952        .    485.2897 5080
"001004" 2012    2136.9     37.6      75.3     86.4     622.2    108.6     11.9    136.6    136.6     245.2        .       .     918.6       0       .   12.1        .    790.0029 5080
"001004" 2013    2199.5     26.5      89.2     69.7     564.3    113.4     11.8    142.6    142.6       256        .       .     999.5       0       .   22.8        .     961.308 5080
"001004" 2014      1515     46.3      54.7       69        85     92.3     11.9     -8.6     -8.6      83.7    -94.2    11.2     845.1       0       .    1.5        .   1046.3954 5080
"001045" 2010     25088     1962      4946     1883      9253      995        0      308      308      1303        .       .     -3945       .       .      .        .   2597.5755 4512
"001045" 2011     23848     1610      4739     1518      6702      981        0     -286     -286       695        .       .     -7111       .       .      .        .    117.3438 4512
"001045" 2012     23510     1888      4742     1419      7116      940        0      494      494      1434        .       .     -7987       .       .      0        .    266.5571 4512
"001045" 2013     42278     3114     10286     1446     15353     1021        0     1935     1935      2956        .       .     -2731       .       .      .        .   6591.9923 4512
"001045" 2014     43771     5311      8077     1708     16196     1512      148     5073     5073      6585        .       .      2021       .       .      .        .  37405.5843 4512
"001045" 2015     48415     6151      6949     2231     18330     1607      278     7284     7284      8891        .       .      5635   12000       .      .        .  26452.7417 4512
"001050" 2013   348.536    1.377    22.661    9.922     79.16    6.647    4.337   18.996   18.996    25.643    5.442   1.013   170.406     5.3   -.052   .623        .    413.2812 3564
"001050" 2014   414.365    1.151    19.362    8.887   103.541   11.268    5.937   24.332   24.332      35.6   14.638   1.576   181.224     4.5   -.706  1.624        .    408.1892 3564
"001050" 2015   598.819     .763    39.513   19.494   157.834    16.52    7.977   28.651   28.651    45.171     .997  -4.093   239.153    40.4    .087  1.944        .    260.4902 3564
"001072" 2009  2051.492   28.888    688.45        0         0   58.173   27.242  169.071  169.071   227.244   56.826 119.531  1801.007 249.629  12.792 11.271    6.831   2415.0508 3670
"001072" 2010  2319.482    27.47   781.011        0         0   47.619   32.314  333.499  333.499   381.118  123.112 211.147  2039.417 246.497    8.54 40.819    7.392   2536.8172 3670
"001072" 2011  2468.012   49.201   813.417        0         0    46.89   44.172  275.551  275.551   322.441    1.463 178.442  2120.753 224.403   2.556 43.471    7.716   2248.9093 3670
"001072" 2012  2601.995   43.705  1047.088        0         0   46.871   50.758  146.405  146.405   193.276 -192.584  79.262   1972.93 263.582   3.794  9.712     7.15   2006.7327 3670
"001072" 2013  2384.988   26.805   874.289        0         0   50.209   60.251  159.164  159.164   209.373   83.837   79.52  2047.685 285.184   -.933 18.325   10.514   2217.1528 3670
"001072" 2014  2459.015   26.599   843.506        0         0   42.214   67.251  212.749  212.749   254.963  114.333 104.266  2131.963 224.104 -51.397 22.189   11.951   2400.0713 3670
"001076" 2010  1502.072   87.636    72.022   13.339    28.451  548.605    3.981  194.761  194.761   743.366        .       .   979.417       0       0      0        .   1632.9739 7359
"001076" 2011  1735.149   78.211    236.66        0   153.789  598.244    4.152  228.118  228.118   826.362        .       .   976.554    31.2       0      0        .   2018.0752 7359
"001076" 2012  1812.929   65.073   173.016    1.755   139.773  661.433     4.71    254.6    254.6   916.033        .       .  1136.126       0       0      0        .   2141.3616 7359
"001076" 2013  1827.176   58.145   286.341   27.529   115.175  685.105    5.479  221.959  221.959   907.064        .       .  1139.963       0       0      0        .   2144.9358 7359
"001076" 2014  2456.844   47.565     24.86  112.597   493.485 1018.234    6.219      176      176  1194.234        .       .  1223.521       0       0      0        .   2215.9582 7359
"001076" 2015  2658.875   60.557    37.168  157.178   453.272 1292.847    6.822  244.117  244.117  1536.964        .       .  1366.618       0       0      0        .    1625.514 7359
"001078" 2010 59462.266 1015.075   7323.94 6394.766 12523.517 2624.305 2731.584 8038.781 8038.781 10663.086     -275    5988 22388.135       0    -142    835 4037.624  74116.0034 2834
"001078" 2011 60276.893   1491.5  8097.359 3374.755 12039.822 3043.894 3011.631 8996.448 8996.448 12040.342      364    4835 24439.833       0    -293   1187 4801.914  88302.4112 2834
"001078" 2012 67234.944 1795.289 15173.984 2390.662 18085.302 2783.207 2649.866 9783.515 9783.515 12566.722     -620    6883 26720.961       0    -645   1230 4610.182 103271.6885 2834
"001078" 2013     42953     1145      8098     3173      3388     1719     1002     2674     2674      4393      529    1992     25171       0    -125    555     1452  59338.5963 2834
"001078" 2014     41275     1077      4460     4437      3408     1548     1363     2927     2927      4475      392    2126     21526       0       4    468     1345  67891.7357 2834
"001078" 2015     41247     1110      6125     3130      5871     1472     1464     3372     3372      4844      789    2394     21211       0     -20    220     1405  66137.3852 2834
"001094" 2010   231.851     3.96    31.185        0       .55    2.796    5.073   14.099   14.099    16.895    3.581   6.622   139.644     3.8    .029  2.003        0     145.628 5160
"001094" 2011   311.665    5.425    29.607    6.247     48.75    5.502    5.213    17.61    17.61    23.112    7.039   9.923   160.821     1.4    .048  2.693        .    178.6202 5160
"001094" 2012    299.28    1.098     26.38    6.713    39.052    6.942     5.35   27.011   27.011    33.953   16.418   8.322   168.003      .6   -.024  2.287        .    243.2411 5160
"001094" 2013    323.43    1.022    35.375   11.714    20.355    6.944    6.057    37.66    37.66    44.604   21.181  13.369    194.64       .    -.01  3.875    2.834    387.6858 5160
"001094" 2014   467.984    1.145    43.643    8.343    97.158    8.092    6.847   47.516   47.516    55.608   30.884   13.79   233.584       .   -.242   4.49    5.222    521.9241 5160
"001094" 2015   489.774     .617    37.436   10.197     99.96   11.849    7.043   53.827   53.827    65.676   48.276   5.589   254.211       .   -.706  2.337    5.942    717.8906 5160
"001121" 2010   301.305   22.421    29.032        0         0   11.817    2.277   12.546   12.546    24.363        .       .    90.155       0       0      0        .    102.5649 5172
"001121" 2011    378.84   53.276    37.066        0         0    16.26    2.404    32.61    32.61     48.87        .       .   110.682       0       0      0        .    122.7902 5172
"001121" 2012   419.501   51.012    47.239        0         0   20.714    2.615   41.846   41.846     62.56        .       .   135.858       0       0      .        .    147.9253 5172
"001121" 2013   448.082   27.602    60.733        0         0   22.275    2.783   34.376   34.376    56.651        .       .   154.685       0       0      .        .     288.933 5172
"001121" 2014   340.814   30.523    80.184        0         0   24.615    3.711    6.953    6.953    31.568        .       .   157.497       0       0      .        .    210.6891 5172
"001121" 2015   243.215   11.074    91.877        0         0   23.717    3.712   -2.359   -2.359    21.358        .       .    152.51       0       0      0        .    161.9712 5172
"001161" 2010      4964      148      1789      233      2188      317        0      492      492       809      987    -478      1013    2953      -5     47     1405     5586.94 3674
"001161" 2011      4954      250      1765      489      1527      246        0      495      495       741      318     173      1590    3621      -6      4     1453      3769.2 3674
"001161" 2012      4000      133      1002        5      2037      193        0       31       31       224    -1242      25       538    5163      -3      6     1354      1711.2 3674
"001161" 2013      4337       84      1097       60      1998      157        0       85       85       242     -397     323       544    5872      -2     10     1201     2805.75 3674
"001161" 2014      3767       95      1040      177      2035      129        0      149      149       278     -621     223       187    6300       0      6     1072     2071.92 3674
"001161" 2015      3109       96       785      230      2032       97        0     -319     -319      -222    -1100     454      -412    6813      -1     16      947     2273.04 3674
"001166" 2010  1611.012  136.636   451.533   75.216   179.291   47.559        0  450.934  450.934   498.493  -63.926 442.706   545.966       .   5.431 50.876   104.54   1853.1493 3559
"001166" 2011  2052.615  115.742    506.27   58.394    195.11   60.939    28.88  348.466  348.466   409.405   -4.476 462.235   855.953 412.064 -36.955 84.555  167.871   1609.2556 3559
"001166" 2012  1977.249   89.878    383.02   89.653    16.657   75.052   36.287  117.549  117.549   192.601  -92.649  180.64   978.238 419.944   -.194 34.728   196.76   2289.1229 3559
"001166" 2013  2137.466   23.511   430.507        0         0   38.591  415.035   60.196   60.196    98.787 1403.465  57.689  1994.164 375.629   -.408 13.061  103.881    2094.444 3559
"001166" 2014  2210.772   37.086   466.829        0         0   25.113   38.515  109.654  109.654   134.767  111.701  75.716  2045.311 254.679  -3.681 17.579   77.275   2667.9542 3559
"001166" 2015  2254.303    36.46   485.305        0         0   42.413    40.35  140.052  140.052   182.465  103.986  60.992  2115.745  174.86  -2.676  3.095   97.443    2344.828 3559
"001186" 2010  5500.351  511.641   203.754   10.592   688.019  195.662  108.009  409.353  409.353   605.015        .       .   3665.45       .  19.845  1.942        .   12940.824 1040
"001186" 2011  5034.262  482.831   366.869   11.068   946.279  266.734     .391 -716.404 -716.404   -449.67        .       .  3202.972       .  65.265  3.718        .   6203.9645 1040
"001186" 2012  5255.842   445.55   376.727   12.955   842.108  280.984  174.849    511.5    511.5   792.484        .       .  3410.212       .  46.104  43.33        .   9028.5758 1040
"001186" 2013  4959.359  577.789   244.622   12.035  1011.843  300.702  114.118  -77.594  -77.594   223.108        .       .  2977.149       .  78.794  44.46        .   4588.9065 1040
"001186" 2014  6840.538  475.412   271.748   74.324  1343.598  447.015   61.653  287.119  287.119   734.134        .       .   4068.49       .       .      .        .    5332.334 1040
"001186" 2015   6683.18  449.758   164.142    24.04  1127.734  614.776   68.762  168.035  168.035   782.811        .       .   4141.02       .       .      .        .   5719.8683 1040
"001209" 2010   13505.9   1030.9     374.3    468.5    3659.8    863.4    408.4     1443     1443    2306.4    464.5   802.6    5546.9   467.6    44.5  140.1    114.7  17707.1645 2810
"001209" 2011   14290.7   1351.7     422.5    634.7    3927.5    873.9    473.8     1628     1628    2501.9    625.5   881.2    5795.8   428.4     1.5  187.9    118.8  16051.8285 2810
"001209" 2012   16941.8     1521     454.4    707.7    4584.2    840.8      529   1489.6   1489.6    2330.4    518.6   640.1    6477.2   481.8   -19.8  173.9    126.4  17571.7652 2810
"001209" 2013   17850.1   1524.2     450.4   1217.3    5056.3      907    579.6   1508.3   1508.3    2415.3    428.5   754.1    7042.1   455.9    -7.1    191    133.7   22505.346 2810
"001209" 2014   17779.1   1684.2     336.6     1294    4824.5    956.9    641.8   1603.7   1603.7    2560.6    555.9   647.2    7365.8   757.4      30  210.5    141.4   27798.507 2810
"001209" 2015   17438.1   1614.8     206.4   1929.9    3949.1    936.4    687.9   1870.3   1870.3    2806.7      739     840      7249   435.2   -12.7  220.1    138.8  27475.5012 2810
"001230" 2010    5016.6      183    1208.2    221.2      1313    230.5        0    484.8    484.8     715.3        .       .    1105.4       .       0      0        .   2036.5316 4512
"001230" 2011      5195    387.4    1140.9    207.9      1099    246.9        0    487.8    487.8     734.7        .       .    1173.2       .       0      0        .   2663.8178 4512
"001230" 2012      5505      518      1252      161       871      264        0      532      532       796        .       .      1421       0       0      0        .   3032.5449 4512
"001230" 2013      5838      566      1330      117       754      270       28      646      646       916        .       .      2029       0       0      0        .    5043.894 4512
"001230" 2014      6181      694      1217      117       686      294       68      932      932      1226        .       .      2127       0       0      0        .   7857.3046 4512
"001230" 2015      6533      831      1328      115       571      320      102     1330     1330      1650        .       .      2411       0       0      0        .  10077.8393 4512
"001234" 2010   134.652    4.293    21.385        0         0    7.041   21.435   30.977   30.977    38.018        .       .   116.617       0       0      0    2.669    361.7914 3841
"001234" 2011   161.895   11.999    44.869        0         0    6.544    3.706   38.168   38.168    44.712        .       .   138.514       0       0      0    2.868    484.3037 3841
"001234" 2012    155.81   10.347    16.181        0         0     7.61   24.617   33.626   33.626    41.236        .       .   134.828       0       0      0    3.766     396.116 3841
"001234" 2013   172.066    7.503     46.91        0         0    8.592     4.85   37.944   37.944    46.536        .       .   148.994       0       0      0    4.288      590.13 3841
"001234" 2014   171.514   12.671    23.859        0         0    8.723    5.464   40.817   40.817     49.54        .       .    149.57       0       0      0    5.286    650.4391 3841
"001234" 2015   164.336    9.323     28.39        0         0    8.823    6.115    42.51    42.51    51.333        .       .   144.098       0       0      0    6.346    695.3088 3841
"001254" 2010      2495       95        14      136       386      107       52      109      109       216        .       .      1136       0       .      .        .    1653.239 4400
"001254" 2011      2544       67        22       52       507      109       53      114      114       223        .       .      1123       0       .      .        .    1702.194 4400
"001254" 2012    1174.3     38.1      19.9     16.4     302.7     71.3     39.5    104.2    104.2     175.5        .       .     279.9       0       .      0        .    1053.072 4400
"001254" 2013    1248.3     35.2     114.5     12.5     273.6     68.2     26.8    102.3    102.3     170.5        .       .     338.2       0       .      0        .    1117.508 4400
"001254" 2014    1401.8     27.9     293.4     21.6       352     68.1     28.7    133.4    133.4     201.5        .       .     363.8       0       .      0        .    1491.264 4400
"001254" 2015    1669.8     67.8      25.5       22     407.9     80.8     30.8    212.1    212.1     292.9        .       .     450.6   406.8       .      0        .    1854.405 4400
"001266" 2010   188.817    8.203    12.365    1.281    72.179    7.221     .725    3.465    3.465    10.686        .       .   105.237       .       0      0        .     171.488  100
"001266" 2011   180.035   12.265     2.325    3.279    53.879    7.327     .882   15.237   15.237    22.564        .       .   110.662       .       0      0        .    144.2165  100
"001266" 2012   185.083   15.921    16.085    3.267    36.633    8.429    1.765   23.742   23.742    32.171        .       .   127.546       .       0      0        .    229.6654  100
"001266" 2013    198.84   18.924    24.843        2        34    9.675    2.626   13.751   13.751    23.426        .       .   142.736       .       0      0        .    300.7057  100
"001266" 2014   203.567   14.508    31.042    2.258    32.839     7.88    1.761   10.156   10.156    18.036        .       .   145.437       .       0      0        .    280.4541  100
"001266" 2015    460.58   11.948      7.36    4.788   201.958   14.637    1.936   32.702   32.702    47.339        .       .   172.792       0       0      0        .    337.9523  100
"001327" 2010  1564.052   88.929   459.385       50    24.743   52.709        0  198.704  198.704   251.413  164.094   30.98  1316.596    19.1    .235   .684   134.14   3726.0362 3674
"001327" 2011  1890.389   100.66   410.799   26.089         0    76.52        0  309.001  309.001   385.521  208.926   84.96  1609.095    61.8    .961   4.34  168.637   3347.4926 3674
"001327" 2012  2136.646   94.129    307.11        0         0  102.289        0   278.86   278.86   381.149   113.14 141.836  1905.475    74.3    .405  8.623  212.534   4530.4938 3674
"001327" 2013    2333.1    123.8     511.1        0         0    103.4        0    353.6    353.6       457    164.8   179.7    2101.1    50.7     -.1   14.8    226.3    4667.436 3674
"001327" 2014    2973.8    208.6     805.8        0         0    122.7     41.7    571.2    571.2     693.9    346.8   218.4    2532.4    21.5   -11.2   13.5    252.2    10983.06 3674
"001327" 2015    3719.4    430.1    1043.6        0         0    195.8      124   1026.7   1026.7    1222.5    602.1   421.5    3159.2     9.9      .4   33.9    303.2   16025.163 3674
"001380" 2010     35396     5492      1608       46      5537     3115      132     2921     2921      6036     -108    3419     16689       .    -230   1515        .  25846.1037 1311
end

[Clyde Schechter]

by gvkey (fyear), sort: egen sd_ebit = sd(ebit)
by gvkey (fyear): replace sd_ebit = . if _N < 3

will solve your first problem.

Your second question cannot be answered because it refers to a variable, PI, which does not exist in your example data.

[Jon Fredheim]

Thank you so much, Clyde! It worked perfectly.

I think I was a little fast when uploading the example data and uploaded from a datafile that didn't have the PI included. The correct datafile is exactly the same just with the variable PI included.
Are you able to help me with this one too without the correct datafile? I can upload another one if needed.

Again, thank you so much for your help.

[Clyde Schechter]

Well, the following code would work if tlcf and pi are never missing.

gen marginal_tax_rate = 0.35 if !missing(tlcf, pi)
replace marginal_tax_rate = 0.175 if (tlcf > 0  & !missing(tlcf)) | (pi < 0)
replace marginal_tax_rate = 0 if tlcf > 0 & !missing(tlcf)  & pi < 0

gen tax_cost_repatriation = max(0, marginal_tax_rate*pifo - txfo) if !missing(marginal_tax_rate, pifo, txfo)

But even in your example data you have many cases where tlcf is missing, so it is not possible to determine the marginal tax rate in those cases. The code here will produce missing values for both the marginal tax rate and the tax cost of repatriation in observations where any of the constituent variables has a missing value.

[Jon Fredheim]

Thank you!

I will most likely drop observations that miss key data, so this can be helpful.