Final and Beginning (Period)

[Hugo Rocha]

Dear all,

I have an urgent question. I am trying to compute average annual growth between a variable (lval_per_worker) in different years (endpoints). I want to compute the difference between annual average growth rate for years 2015 and 1980 for the variable lval_per_worker ( I only want the difference between the end points) over the difference in years (2015-1980=35). Later, I want to regress the average annual growth rate (variable I want to construct) over the log of value added per worker in 1980 (lval_per_worker in 1980). My data includes country, isic (industry) and years (unbalanced panel).

avg annual growth rate (lval_per_worker_2015- lval_per_worker_1980)= C+ \beta*lval_per_worker_1980 (OLS).

 * Example generated by -dataex-. To install: ssc install dataex
clear
input int country1 str2 isic int year double ValueAdded long Employment float(r_valworker lval_worker ln_1980 perc_wanted2)
398 "18" 1994           .       .         .         .         0            .
400 "22" 2011   139139437    5745 12046.368  9.396519  9.251294   .004684663
705 "21" 1985           .       .         .         .         0            .
524 "21" 2008      658341     873  397.7566   5.98584         0            .
 60 "22" 1999           .     390         .         .         0            .
705 "21" 2006   173368275    5226 20136.074  9.910268         0            .
266 "19" 1970           .       .         .         .         0            .
231 "21" 2001     6275383    1357  3446.155  8.145015         0            .
388 "18" 1991           .       .         .         .         0            .
410 "19" 2014  1905086243   18218  50931.88 10.838244         0            .
438 "16" 2011           .       0         .         .         0            .
178 "15" 1970    17279109   11099 4219.0156  8.347357         0            .
894 "21" 1988           .       .         .         .  9.881193            .
422 "20" 2008   227872079       .         .         .         0            .
462 "20" 2006           .       .         .         .         0            .
360 "21" 1987   127140683   26400  4684.382  8.451989  8.297654    .02204786
340 "15" 1993   204757476   31821   5411.45  8.596272         0            .
250 "21" 2010  5430930955   68254  43072.58 10.670642  10.40521   .008847745
764 "19" 2010   656890087  114280  3111.553  8.042877         0            .
 50 "17" 1971    95185531  164400 1519.3197  7.326018  7.223282            .
616 "22" 1983   188967777   44000 4241.7007  8.352719  8.179547      .057724
214 "22" 1998           .       .         .         .  9.139141            .
 70 "16" 1994           .     440         .         .         0            .
120 "15" 1993   200874000   20980  8052.041  8.993681  9.822526   -.06375731
508 "21" 2013           .       .         .         .         0            .
800 "16" 1993     7676880     764  8450.436  9.041973         0            .
686 "17" 2014     3017008    2180  674.0557  6.513313   9.26582   -.08095608
280 "17" 1981  6750882719  296000  23264.57 10.054687 10.104993   -.05030632
530 "19" 1991           .       .         .         .         0            .
268 "20" 1999           .    1797         .         .         0            .
 12 "16" 1984           .       .         .         .  9.920269            .
360 "19" 1993           .       .         .         .         0            .
192 "18" 1975           .       .         .         .  8.889088            .
356 "16" 2014  2543031885  437107 2833.6084  7.949306  6.411442     .0452313
446 "15" 1999    14374894    1244   9209.31   9.12797  7.995726     .0595918
630 "22" 1974           .    3030         .         .         0            .
376 "21" 1986   174966831    6000  29112.62 10.278927 10.303637 -.0041182837
360 "17" 2004  2951242045  546145 3684.1794  8.211803  7.618095   .024737835
894 "15" 1996           .       .         .         .  9.671973            .
112 "15" 2002           .       .         .         .         0            .
531 "19" 2014           .       .         .         .         0            .
 40 "15" 1974   759128322   62900  22565.56  10.02418 10.241074            .
590 "22" 2007           .    3392         .         .  9.464402            .
705 "19" 2015    69899345    3612  10161.63  9.226374         0            .
484 "22" 1993   192442139    9438 17147.781  9.749624         0            .
196 "22" 1976     5975698     991  9863.634 9.1966095  9.373387            .
450 "21" 1981     4184294    1072 3981.5635   8.28943   8.62533    -.3359003
372 "17" 1963    49839242   21900  7207.489  8.882875  9.754238            .
288 "21" 1966     2000000     554 10838.454  9.290855  8.529375            .
704 "22" 2004           .   40218         .         .         0            .
170 "22" 1975    71227784   17500  6967.462  8.849007   9.24948            .
 32 "22" 1990   694786798   27848 21454.033  9.973668         0            .
702 "21" 2014   198668561    3638  26597.59 10.188576  9.356116     .0244841
266 "21" 1991      308394       .         .         .  9.535266            .
554 "15" 1999  2834970528   60702 37221.027  10.52463  9.834158   .036340613
706 "20" 1971           0       0         .         .         0            .
428 "18" 2005    72261586   13291  3454.181  8.147341         0            .
604 "17" 1996   499464198   27567 14189.943  9.560288         0            .
590 "22" 1973    10510000    1737  13438.45  9.505876  9.464402            .
840 "18" 1963   8.870e+09 1459000 19254.178  9.865483     9.898            .
686 "22" 2014     8395717    1172   3489.04  8.157382  9.650778   -.04392341
388 "15" 1976   193050193   17341  18210.34  9.809745  9.204855            .
380 "15" 1997 17883048196  308313  45456.82 10.724518 10.678854   .002686108
710 "16" 1982    86570837    5000 17311.281  9.759113 10.333258   -.28707218
  8 "18" 2019   148923869   28916  2577.152   7.85444         0            .
724 "16" 1984   464042898   11000  40690.35 10.613747 11.005033   -.09782147
688 "22" 1997           .       .         .         .         0            .
752 "18" 1977   370399956   25200  22656.51 10.028202 10.031988            .
756 "21" 1993   986047129   15700  52818.46 10.874616         0            .
230 "16" 1970     4804000     432  30136.51 10.313493 10.653697            .
894 "19" 1974           .       .         .         .         0            .
152 "16" 1974   170370395    1530  208201.7 12.246263 12.534375            .
356 "22" 1990   339817751  142527 2050.2195  7.625702  7.563256   .006244612
 32 "20" 1977           .   56761         .         .         0            .
862 "16" 1985   596666667    3700 156336.66 11.959767 11.537888    .08437596
716 "20" 2013   152589397       .         .         .         0            .
230 "17" 1971    33160850   21409 4064.5205  8.310051  8.299515            .
480 "17" 2006    64933003    6813  5784.978   8.66302  8.413436   .009599392
 68 "19" 1987           .       .         .         .         0            .
 44 "15" 1996    61308600     876  54812.94  10.91168         0            .
340 "21" 1985     9307000    1431  6305.229  8.749135         0            .
 40 "22" 2011  1434827341   13325   53558.5  10.88853 10.158862    .02353767
340 "15" 1965    22690000    5127 13715.672  9.526295         0            .
 68 "16" 2016           .       .         .         . 11.706547            .
807 "19" 2010    20494041    4417  2511.626  7.828686         0            .
140 "15" 1991    29811393    1239  20647.18  9.935334    9.1395   .072348595
 32 "22" 2012  3292850855       .         .         .         0            .
462 "20" 2016           .       .         .         .         0            .
158 "21" 2011  1646073386   49368 16584.393  9.716217         0            .
776 "20" 1993           .      43         .         .         0            .
634 "16" 1999           .       .         .         .         0            .
398 "19" 1994           .       .         .         .         0            .
686 "20" 1990      558280      69  6957.519  8.847578  9.030405    -.0182827
203 "20" 2002   552784632   46025  9160.186  9.122622         0            .
278 "21" 1978           .   93900         .         .         0            .
 32 "15" 2005  8826431005       .         .         .         0            .
840 "20" 1976   1.011e+10  491000 33681.516 10.424705 10.247317            .
 60 "22" 2009           .     289         .         .         0            .
 52 "15" 1978    18862000    1992 13538.247  9.513274  9.346107            .
 60 "18" 2003           .      17         .         .         0            .
end

Initially I tried the following code but it includes more points than the years I want ( how can I only include lval_per_worker_2015 and lval_per_worker_1980 to calculate perc_wanted?). r_valworker is calculated by picking value added and dividing it by employment (deflating using the Producer Price Index) (r_val_worker)

 bysort country isic: g ln_1980=ln(r_valworker) if year==1980
replace ln_1980=0 if ln_1980==.
gen check=0 if ln_1980!=0
sort country isic check
drop check
bysort country isic : replace ln_1980=sum( ln_1980)
g lval_per_worker=ln(r_valworker)
bysort country isic year: gen perc_wanted= (lval_per_worker-ln_1980)/abs(year-1980) if year>1980 &ln_1980!=0

Thank you so much!

Once I create variables to regress each variable, I find no observations because columns do not match if I do the following:

 bysort country isic: g ln_1980=ln(r_valworker) if year==1980

 bysort country isic: g ln_2015=ln(r_valworker) if year==205

[Nick Cox]

The data example is an extraordinary jumble and the impression is that you often don't have data for both 1980 and 2015. But the main problem, as you realise, is that you are never going to have observations in which the year is both 1980 and 2015. so

bysort country isic: g ln_1980=ln(r_valworker) if year==1980   bysort country isic: g ln_2015=ln(r_valworker) if year==2015

will at best create variables that are missing in one variable but not in the either, that is, the 2015 variable will be missing if the 1980 variable is non-missing and vice versa. A direct solution that spreads the 1980 and 2015 values to all observations in a subset is (example for 1980, obvious variation for 2015)

bysort country isic: egen ln_1980 = mean(cond(year == 1980, ln(r_valworker), .))

where other egen functions such as max() min() total() will work just as well if it is a matter of identifying the single observation that qualifies, when it exists.

See Section 9 in https://www.stata-journal.com/articl...article=dm0055 for more on this.

Claims of urgency often get you nowhere or even backfire as what is urgent for you is not urgent for us, and do you think your problem is more deserving than anyone else's?
There is a subdued hint about this at https://www.statalist.org/forums/help#technical

[Hugo Rocha]

I do apologize for the "urgent" word and violating the standards in the forum. This will not happen again. My sincere apologies.

The panel is unbalanced for many countries from 1963 until 2019. My goal is just to obtain the log of real value added per worker ln(r_valworker) per isic year in 1980 and 2019. But once, I run your proposed code as follows:

 bysort country isic: egen ln_1980= mean (cond(year==1980, ln(r_valworker),.))

 bysort country isic: egen ln_2019= mean(cond(year==2019, ln(r_valworker),.))

I think I obtain a larger number of values than those desired

 sum ln_1980 ln_2019

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
     ln_1980 |     70,308    9.515807    1.001174   6.411442   13.51965
     ln_2015 |     79,454    9.802311    1.185937   4.090506   18.97665

When I should be getting something like this

 sum lval_worker if year==1970&2019

    Variable |        Obs        Mean    Std. Dev.       Min        Max
-------------+---------------------------------------------------------
 lval_worker |      1,303    9.200088    .9300469   4.885692   13.10271

What I do want are just the end values, the values for lval_worker per country isic for year 2015 and lval_worker per country isic for year 1980. For some reason, all I am trying is including all years in between. The goal, at the end, is to subtract (ln_2019-ln_1980) and divide by the number of years to obtain the average annual growth measure...

[Nick Cox]

if year == 1980 & 2019

is legal but unlikely to be whatever it is you want, as it is parsed

if (year == 1980) & 2019

which is true if and only if year is 1980 -- as 2019 counts as being true (as being not zero).

This is the same fallacy as before. year cannot be 1980 and 2019 in the same observation.

If you intend the syntax to mean something else, then your meaning isn't captured by the syntax.

The point of my syntax is to place values for two years alongside each other so you can then calculate the growth rate.

[Hugo Rocha]

Yes, I did not notice that minor detail about my first syntax. I actually intended to show the number of observations for log of real value added per worker for years 1980 and 2015, which is what I get with your second syntax

What I did, after you shared me your sintax in thread #2, was constructing average annual growth as follows:

 gen av_ann_growth= (ln_2015-ln_1980)/abs(2015-1980)

Then, I try the regression (my first goal):

 reg avg_ann_growth ln_1980 i.isic1 i.country1 i.year, robust

Linear regression                               Number of obs     =     42,816
                                                F(125, 42690)     =     477.17
                                                Prob > F          =     0.0000
                                                R-squared         =     0.4566
                                                Root MSE          =     .01653

------------------------------------------------------------------------------
             |               Robust
avg_ann_gr~h |      Coef.   Std. Err.      t    P>|t|     [95% Conf. Interval]
-------------+----------------------------------------------------------------
     ln_1980 |  -.0227372   .0003821   -59.51   0.000    -.0234861   -.0219882
             |
       isic1 |
         16  |   .0308025   .0008056    38.24   0.000     .0292235    .0323814
         17  |  -.0127341   .0002714   -46.92   0.000    -.0132661   -.0122022
         18  |  -.0210025   .0003481   -60.34   0.000    -.0216847   -.0203203
         20  |  -.0124896   .0003861   -32.35   0.000    -.0132463   -.0117329
         21  |   .0008178   .0002659     3.08   0.002     .0002967    .0013389
         22  |  -.0060067   .0002855   -21.04   0.000    -.0065663    -.005447
         23  |   .0511611   .0013467    37.99   0.000     .0485215    .0538007
         24  |   .0140539   .0003885    36.17   0.000     .0132924    .0148153
         25  |  -.0040247   .0002341   -17.19   0.000    -.0044836   -.0035657
         26  |   .0029421   .0002579    11.41   0.000     .0024366    .0034475
         27  |   .0077963   .0003694    21.11   0.000     .0070723    .0085203
         28  |   -.007943   .0004239   -18.74   0.000    -.0087738   -.0071121
         29  |    .000567   .0002614     2.17   0.030     .0000546    .0010794
         31  |  -.0024153   .0004834    -5.00   0.000    -.0033629   -.0014678
         33  |   .0035149   .0012329     2.85   0.004     .0010984    .0059314
         34  |   .0030223   .0003284     9.20   0.000     .0023787     .003666
         36  |  -.0109602   .0004032   -27.18   0.000    -.0117506   -.0101699
             |
    country1 |
         36  |   .0051732   .0021495     2.41   0.016       .00096    .0093863
         40  |   .0094385   .0021519     4.39   0.000     .0052207    .0136564
         56  |   .0101955    .002096     4.86   0.000     .0060873    .0143037
        108  |  -.0392045   .0028991   -13.52   0.000    -.0448868   -.0335223
        124  |   .0124904   .0020995     5.95   0.000     .0083754    .0166054
        144  |  -.0345038   .0027557   -12.52   0.000     -.039905   -.0291025
        152  |  -.0129779   .0021766    -5.96   0.000    -.0172441   -.0087117
        170  |   -.013671     .00225    -6.08   0.000    -.0180811   -.0092608
        196  |  -.0159146   .0022017    -7.23   0.000      -.02023   -.0115993
        208  |   .0044997   .0021083     2.13   0.033     .0003673    .0086321
        218  |  -.0179906    .002267    -7.94   0.000    -.0224339   -.0135472
        242  |  -.0300461   .0023167   -12.97   0.000     -.034587   -.0255052
        246  |   .0058796   .0021016     2.80   0.005     .0017605    .0099987
        250  |   .0033824   .0020839     1.62   0.105    -.0007021     .007467
        288  |  -.0125298   .0024745    -5.06   0.000      -.01738   -.0076796
        300  |  -.0106126   .0022025    -4.82   0.000    -.0149296   -.0062956
        344  |  -.0062962   .0024171    -2.60   0.009    -.0110337   -.0015587
        348  |  -.0203421   .0023732    -8.57   0.000    -.0249937   -.0156905
        352  |   .0052674   .0021524     2.45   0.014     .0010486    .0094862
        356  |  -.0373101   .0026367   -14.15   0.000    -.0424782    -.032142
        360  |  -.0150255   .0024862    -6.04   0.000    -.0198985   -.0101526
        364  |  -.0288198   .0022322   -12.91   0.000    -.0331948   -.0244447
        372  |   .0099362   .0021406     4.64   0.000     .0057406    .0141318
        376  |  -.0029334   .0021514    -1.36   0.173    -.0071503    .0012834
        380  |  -.0006791   .0021477    -0.32   0.752    -.0048887    .0035305
        392  |   .0051466   .0021524     2.39   0.017     .0009279    .0093654
        400  |   -.016454   .0022784    -7.22   0.000    -.0209197   -.0119883
        404  |  -.0328339      .0027   -12.16   0.000    -.0381261   -.0275418
        410  |   .0162152   .0022791     7.11   0.000     .0117482    .0206822
        414  |  -.0179877    .002183    -8.24   0.000    -.0222664   -.0137091
        446  |  -.0077592   .0026599    -2.92   0.004    -.0129726   -.0025458
        458  |  -.0195688   .0023406    -8.36   0.000    -.0241564   -.0149813
        470  |  -.0175914   .0024576    -7.16   0.000    -.0224083   -.0127744
        480  |   -.021249    .002479    -8.57   0.000    -.0261079   -.0163902
        504  |  -.0293402    .002384   -12.31   0.000    -.0340129   -.0246676
        528  |   .0083719    .002125     3.94   0.000     .0042069    .0125369
        554  |   .0028152   .0021852     1.29   0.198    -.0014679    .0070982
        578  |   .0131924   .0021022     6.28   0.000     .0090721    .0173127
        608  |  -.0299265   .0024216   -12.36   0.000    -.0346728   -.0251802
        616  |  -.0160837   .0023796    -6.76   0.000    -.0207478   -.0114197
        620  |  -.0067782   .0023153    -2.93   0.003    -.0113162   -.0022403
        702  |   .0027664   .0022623     1.22   0.221    -.0016678    .0072006
        724  |  -.0010986   .0021398    -0.51   0.608    -.0052926    .0030954
        752  |   .0086709   .0020999     4.13   0.000      .004555    .0127868
        788  |  -.0176334   .0024427    -7.22   0.000    -.0224211   -.0128457
        792  |  -.0235136   .0022296   -10.55   0.000    -.0278836   -.0191435
        818  |  -.0297323   .0026079   -11.40   0.000     -.034844   -.0246207
        826  |   .0099135   .0021084     4.70   0.000      .005781     .014046
        834  |  -.0382444   .0025508   -14.99   0.000    -.0432441   -.0332448
        840  |    .027025   .0020939    12.91   0.000      .022921    .0311291
        858  |  -.0147285   .0022927    -6.42   0.000    -.0192221   -.0102348
             |
        year |
       1964  |  -1.08e-15   .0007698    -0.00   1.000    -.0015087    .0015087
       1965  |  -1.06e-15   .0007645    -0.00   1.000    -.0014985    .0014985
       1966  |  -1.16e-15   .0007612    -0.00   1.000     -.001492     .001492
       1967  |  -1.25e-15   .0008576    -0.00   1.000     -.001681     .001681
       1968  |  -1.25e-15   .0008189    -0.00   1.000     -.001605     .001605
       1969  |  -1.26e-15   .0008253    -0.00   1.000    -.0016176    .0016176
       1970  |  -1.24e-15   .0008132    -0.00   1.000     -.001594     .001594
       1971  |  -1.24e-15   .0008132    -0.00   1.000     -.001594     .001594
       1972  |  -1.24e-15   .0008188    -0.00   1.000    -.0016048    .0016048
       1973  |  -1.24e-15   .0008172    -0.00   1.000    -.0016018    .0016018
       1974  |  -1.24e-15   .0008118    -0.00   1.000    -.0015911    .0015911
       1975  |  -1.24e-15   .0008118    -0.00   1.000    -.0015911    .0015911
       1976  |  -1.23e-15   .0008117    -0.00   1.000     -.001591     .001591
       1977  |  -1.23e-15   .0008117    -0.00   1.000     -.001591     .001591
       1978  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       1979  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       1980  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       1981  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1982  |  -1.24e-15   .0008094    -0.00   1.000    -.0015864    .0015864
       1983  |  -1.24e-15   .0008096    -0.00   1.000    -.0015869    .0015869
       1984  |  -1.24e-15   .0008094    -0.00   1.000    -.0015864    .0015864
       1985  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1986  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       1987  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       1988  |  -1.23e-15   .0008059    -0.00   1.000    -.0015795    .0015795
       1989  |  -1.24e-15   .0008086    -0.00   1.000    -.0015849    .0015849
       1990  |  -1.23e-15   .0008059    -0.00   1.000    -.0015795    .0015795
       1991  |  -1.23e-15   .0008059    -0.00   1.000    -.0015795    .0015795
       1992  |  -1.24e-15   .0008082    -0.00   1.000    -.0015841    .0015841
       1993  |  -1.24e-15   .0008097    -0.00   1.000     -.001587     .001587
       1994  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1995  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1996  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1997  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1998  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       1999  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       2000  |  -1.24e-15   .0008097    -0.00   1.000     -.001587     .001587
       2001  |  -1.24e-15   .0008097    -0.00   1.000     -.001587     .001587
       2002  |  -1.24e-15   .0008097    -0.00   1.000     -.001587     .001587
       2003  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       2004  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       2005  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       2006  |  -1.24e-15   .0008068    -0.00   1.000    -.0015814    .0015814
       2007  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2008  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2009  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2010  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2011  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2012  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2013  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2014  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2015  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2016  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2017  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2018  |  -1.24e-15   .0008071    -0.00   1.000    -.0015819    .0015819
       2019  |  -1.25e-15   .0008222    -0.00   1.000    -.0016115    .0016115
             |
       _cons |   .2411753   .0050474    47.78   0.000     .2312823    .2510684
------------------------------------------------------------------------------

And I find that I have dummies for years, I haven't imagined (years before 1980). And I also I have many repeated values (obviously, the values for ln_1980 and ln_2015 repeat for all years)