Dividing the panel data

[Muhammad Salahuddin]

Dear all,
The data are longitudinal quarterly data sets. Here, I want to change the base year from 2015 to 2010. As the new-base year of this index data, the data need to divide by the 2010 data. From a new base year, quarterly-1 (2011q1) will be divided by 2010q1, such as 2013q1 and 2013q2 will be divided by 2010q1 and 2010q2 respectively. How can I do that? Thanks


input float(id qdate) double(emp gwage) float indp double(exp_index imp_index indpturnover ex_rate) str154 industry byte _merge
7 200 67.4 32.5 71.53333 93 78.6 90.8 118.86 "B07. (Mining Of Metal Ores)" 3
7 201 82.5 38.5 78.16666 100.7 85.7 102.93333333333334 121.6 "B07. (Mining Of Metal Ores)" 3
7 202 87.9 42 74.13333 97.4 116.8 99.63333333333333 123.2 "B07. (Mining Of Metal Ores)" 3
7 203 87.4 52.4 73.86667 108.9 119 106.7 120.22 "B07. (Mining Of Metal Ores)" 3
7 204 84.4 44.4 72.73333 114.5 130.3 110.03333333333335 111.68 "B07. (Mining Of Metal Ores)" 3
7 205 97 51.5 78 123 139.4 113.40000000000002 109.16 "B07. (Mining Of Metal Ores)" 3
7 206 104.3 56.5 94.8 130.1 166.7 165.6 99.89 "B07. (Mining Of Metal Ores)" 3
7 207 101.8 60.8 99.2 127.6 184.5 178.79999999999998 103.56 "B07. (Mining Of Metal Ores)" 3
7 208 97.8 59.7 84.5 121.8 155.2 177.9 108.3 "B07. (Mining Of Metal Ores)" 3

[William Lisowski]

I find what you propose to do unusual. Do you realize that what you propose to do will change, for each changed index, all four quarters of the 2010 data to be equal to 1 (or since it is an index you will likely rescale it from 1 to 100)? Since 2015 is the current base year, are all four quarters of the 2015 data currently equal to 1 (or 100) or do they take four different values?

If not, you show us very little of your data. Can you do

dataex if id==7

and post the results so that we can see the full range of data for a single panel? (I am assuming id 7 does not have data for more than one industry.)

If you are running version 15.1 or later, or a fully updated version 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it.

[Muhammad Salahuddin]

I find what you propose to do unusual. Do you realize that what you propose to do will change, for each changed index, all four quarters of the 2010 data to be equal to 1 (or since it is an index you will likely rescale it from 1 to 100)? Since 2015 is the current base year, are all four quarters of the 2015 data currently equal to 1 (or 100) or do they take four different values?

If not, you show us very little of your data. Can you do

dataex if id==7

and post the results so that we can see the full range of data for a single panel? (I am assuming id 7 does not have data for more than one industry.)

If you are running version 15.1 or later, or a fully updated version 14.2, dataex is already part of your official Stata installation. If not, run ssc install dataex to get it. Either way, run help dataex and read the simple instructions for using it.

I get your point. The single point of 2015q1,or q2 is not 100, the sum, however, of the base year (2015) quarters is 100, . When we are going to change the base year from 2015 to 2010. We need to coordinate with 2010q1 to 2011q1, 2010q2 to 2011q2

the formula will be like this: the value of 2011q1 = (2011q1/2010q1)x 100, the same way for 2011q2 =(2011q2/2010q2)x100, for 2013q1=(2013q1/2010q1)x100

Now, does it make sense?



dataex if id==7

----------------------- copy starting from the next line -----------------------

  
* Example generated by -dataex-. To install: ssc install dataex
clear
input float(id qdate) double(emp gwage) float indp double(exp_index imp_index indpturnover ex_rate) str154 industry byte _merge
7 200  67.4  32.5  71.53333     93   78.6               90.8 118.86 "B07. (Mining Of Metal Ores)" 3
7 201  82.5  38.5  78.16666  100.7   85.7 102.93333333333334  121.6 "B07. (Mining Of Metal Ores)" 3
7 202  87.9    42  74.13333   97.4  116.8  99.63333333333333  123.2 "B07. (Mining Of Metal Ores)" 3
7 203  87.4  52.4  73.86667  108.9    119              106.7 120.22 "B07. (Mining Of Metal Ores)" 3
7 204  84.4  44.4  72.73333  114.5  130.3 110.03333333333335 111.68 "B07. (Mining Of Metal Ores)" 3
7 205    97  51.5        78    123  139.4 113.40000000000002 109.16 "B07. (Mining Of Metal Ores)" 3
7 206 104.3  56.5      94.8  130.1  166.7              165.6  99.89 "B07. (Mining Of Metal Ores)" 3
7 207 101.8  60.8      99.2  127.6  184.5 178.79999999999998 103.56 "B07. (Mining Of Metal Ores)" 3
7 208  97.8  59.7      84.5  121.8  155.2              177.9  108.3 "B07. (Mining Of Metal Ores)" 3
7 209 109.1    66  98.46667  119.7  145.4 163.20000000000002 110.11 "B07. (Mining Of Metal Ores)" 3
7 210 111.9  73.9  99.73333  120.3  143.7 169.03333333333333 109.74 "B07. (Mining Of Metal Ores)" 3
7 211 102.7  71.4  98.46667  116.9  116.3              187.5 111.17 "B07. (Mining Of Metal Ores)" 3
7 212  94.1  67.9  95.33334 120.86 125.89 177.70000000000002 112.88 "B07. (Mining Of Metal Ores)" 3
7 213 104.3  73.8 119.43333 122.72 137.52 168.13333333333333  108.1 "B07. (Mining Of Metal Ores)" 3
7 214 105.9    81     124.7 130.79 141.68 181.96666666666667 102.87 "B07. (Mining Of Metal Ores)" 3
7 215 103.6  89.6 124.33334 135.07 150.32  191.4333333333333 101.09 "B07. (Mining Of Metal Ores)" 3
7 216 102.1  79.4 106.16667 146.85 168.23 193.03333333333333  97.43 "B07. (Mining Of Metal Ores)" 3
7 217 106.2  86.3     108.7 135.61 147.25  167.9333333333333 104.61 "B07. (Mining Of Metal Ores)" 3
7 218 106.2  90.8     113.6 143.26  130.9 174.29999999999998  103.6 "B07. (Mining Of Metal Ores)" 3
7 219 103.8  93.3     111.1 144.12 126.17  187.0333333333333 105.86 "B07. (Mining Of Metal Ores)" 3
7 220    97  96.1  90.46667 139.31 127.82 149.53333333333333 104.75 "B07. (Mining Of Metal Ores)" 3
7 221 102.4  97.1     101.2 160.93 114.49 168.76666666666668  99.19 "B07. (Mining Of Metal Ores)" 3
7 222 103.3 105.6     106.3  158.5 109.22              166.9  91.95 "B07. (Mining Of Metal Ores)" 3
7 223  97.2 101.2       102 136.29 106.77                182  99.17 "B07. (Mining Of Metal Ores)" 3
7 224  83.6 101.1      81.8 137.22   98.8 154.33333333333334 102.64 "B07. (Mining Of Metal Ores)" 3
7 225  86.2  98.8  93.03333 139.23 103.97 148.36666666666665 101.86 "B07. (Mining Of Metal Ores)" 3
7 226  89.2   111      93.8 154.06 100.78 177.93333333333337 101.52 "B07. (Mining Of Metal Ores)" 3
7 227    94 113.5     105.5 204.77 116.96  217.9333333333333  93.59 "B07. (Mining Of Metal Ores)" 3
7 228  95.5 124.5      96.5  245.9 176.75 219.73333333333335  90.89 "B07. (Mining Of Metal Ores)" 3
7 229 103.8 124.7 101.23333 221.03 193.08 215.73333333333335  92.86 "B07. (Mining Of Metal Ores)" 3
7 230 107.6 136.5 114.43333 211.08 154.48 223.16666666666666  91.67 "B07. (Mining Of Metal Ores)" 3
7 231   109   144 117.16667 225.75 187.91              227.5  86.34 "B07. (Mining Of Metal Ores)" 3
end
format %tq qdate

[William Lisowski]

Actually, let's look at the sum of your quarters in each year.

. generate year = yofd(dofq(qdate))

. collapse (sum) emp-ex_rate, by(id year)

. list, clean noobs abbreviate(12)

    id   year     emp   gwage       indp   exp_index   imp_index   indpturnover   ex_rate  
     7   2010   325.2   165.4      297.7         400       400.1      400.06667    483.88  
     7   2011   387.5   213.2   344.7333       495.2       620.9      567.83333    424.29  
     7   2012   421.5     271   381.1667       478.7       560.6      697.63333    439.32  
     7   2013   407.9   312.3      463.8      509.44      555.41      719.23333    424.94  
     7   2014   418.3   349.8   439.5667      569.84      572.55          722.3     411.5  
     7   2015   399.9     400   399.9667      595.03       458.3          667.2    395.06  
     7   2016     353   424.4   374.1333      635.28      420.51      698.56667    399.61  
     7   2017   415.9   529.7   429.3333      903.76      712.22      886.13333    361.76

What we see is that for the variables emp, gwage, and indp, the total for the base year 2015 is 400, so the average of the four quarters is 100. That makes sense to me.

We also see by that standard that the variables exp_index, imp_index, and indpturnover apparently already have 2010 as their base year, so we need do nothing to those values.

Finally, it is not clear to me if ex_rate has a base year - the most likely candidate seems to be 2016 with a total of 399.61.

I do not believe that what you propose to do is correct, given that your current base years are characterized by the sum of the four quarters being 100. If you use

new2011q1 = (2011q1/2010q1) x 100

as your guidance for the new value of 2011 using 2010 as a base year, then for 2010 applying the same logic gives

new2010q1 = (2010q1/2010q1) x 100 = 100

and each quarter of 2010 will be the same, although their sum will indeed be 400.

To change the base year for a variable to 2010, multiply the variable by 400 divided by the sum of its four quarters in 2010. (Example: emp = emp * 400/325.2) Then the sum of the four new values for 2010 will be 400 without them each being 100,

. drop industry _merge // not needed for this example

. generate year = yofd(dofq(qdate))

. foreach var of varlist emp gwage indp {
  2.     egen t2010 = total(cond(year==2010,`var',0))
  3.         replace `var' = `var' * 400 / t2010
  4.         drop t2010
  5.         }
(32 real changes made)
(32 real changes made)
(32 real changes made)

. list, clean noobs abbreviate(12)

    id    qdate         emp       gwage       indp   exp_index   imp_index   indpturnover   ex_rate   year  
     7   2010q1   82.902826   78.597343   96.11466          93        78.6           90.8    118.86   2010  
     7   2010q2   101.47601   93.107621   105.0274       100.7        85.7      102.93333     121.6   2010  
     7   2010q3   108.11808   101.57195   99.60811        97.4       116.8      99.633333     123.2   2010  
     7   2010q4   107.50307    126.7231   99.24981       108.9         119          106.7    120.22   2010  
     7   2011q1   103.81303   107.37606   97.72702       114.5       130.3      110.03333    111.68   2011  
     7   2011q2   119.31119   124.54656   104.8035         123       139.4          113.4    109.16   2011  
     7   2011q3   128.29028   136.63846   127.3766       130.1       166.7          165.6     99.89   2011  
     7   2011q4   125.21525   147.03749   133.2885       127.6       184.5          178.8    103.56   2011  
     7   2012q1    120.2952   144.37727   113.5371       121.8       155.2          177.9     108.3   2012  
     7   2012q2   134.19434   159.61307   132.3032       119.7       145.4          163.2    110.11   2012  
     7   2012q3   137.63837   178.71827   134.0052       120.3       143.7      169.03333    109.74   2012  
     7   2012q4   126.32226   172.67232   132.3032       116.9       116.3          187.5    111.17   2012  
     7   2013q1   115.74415   164.20799   128.0932      120.86      125.89          177.7    112.88   2013  
     7   2013q2   128.29028   178.47643   160.4747      122.72      137.52      168.13333     108.1   2013  
     7   2013q3    130.2583   195.88876   167.5512      130.79      141.68      181.96667    102.87   2013  
     7   2013q4   127.42927   216.68683   167.0586      135.07      150.32      191.43333    101.09   2013  
     7   2014q1   125.58425   192.01935   142.6492      146.85      168.23      193.03333     97.43   2014  
     7   2014q2    130.6273   208.70617   146.0531      135.61      147.25      167.93333    104.61   2014  
     7   2014q3    130.6273   219.58888   152.6369      143.26       130.9          174.3     103.6   2014  
     7   2014q4   127.67527   225.63483   149.2778      144.12      126.17      187.03333    105.86   2014  
     7   2015q1   119.31119    232.4063   121.5541      139.31      127.82      149.53333    104.75   2015  
     7   2015q2   125.95325   234.82468   135.9758      160.93      114.49      168.76667     99.19   2015  
     7   2015q3   127.06027    255.3809   142.8284       158.5      109.22          166.9     91.95   2015  
     7   2015q4   119.55719   244.74003   137.0507      136.29      106.77            182     99.17   2015  
     7   2016q1   102.82902    244.4982   109.9093      137.22        98.8      154.33333    102.64   2016  
     7   2016q2   106.02706   238.93592   125.0028      139.23      103.97      148.36667    101.86   2016  
     7   2016q3   109.71709   268.44016   126.0329      154.06      100.78      177.93333    101.52   2016  
     7   2016q4   115.62115    274.4861   141.7534      204.77      116.96      217.93333     93.59   2016  
     7   2017q1   117.46617   301.08828   129.6607       245.9      176.75      219.73333     90.89   2017  
     7   2017q2   127.67527   301.57196   136.0206      221.03      193.08      215.73333     92.86   2017  
     7   2017q3   132.34932   330.10884   153.7566      211.08      154.48      223.16667     91.67   2017  
     7   2017q4   134.07134   348.24669   157.4292      225.75      187.91          227.5     86.34   2017  

. preserve

. collapse (sum) emp-ex_rate, by(id year)

. list, clean noobs abbreviate(12)

    id   year         emp       gwage       indp   exp_index   imp_index   indpturnover   ex_rate  
     7   2010   399.99998   400.00001        400         400       400.1      400.06667    483.88  
     7   2011   476.62975   515.59857   463.1956       495.2       620.9      567.83333    424.29  
     7   2012   518.45017   655.38092   512.1487       478.7       560.6      697.63333    439.32  
     7   2013     501.722      755.26   623.1777      509.44      555.41      719.23333    424.94  
     7   2014   514.51413   845.94925    590.617      569.84      572.55          722.3     411.5  
     7   2015    491.8819   967.35191   537.4091      595.03       458.3          667.2    395.06  
     7   2016   434.19433   1026.3604   502.6985      635.28      420.51      698.56667    399.61  
     7   2017    511.5621   1281.0158   576.8671      903.76      712.22      886.13333    361.76  

. restore

.

[Muhammad Salahuddin]

Actually, let's look at the sum of your quarters in each year.

. generate year = yofd(dofq(qdate))

. collapse (sum) emp-ex_rate, by(id year)

. list, clean noobs abbreviate(12)

id year emp gwage indp exp_index imp_index indpturnover ex_rate
7 2010 325.2 165.4 297.7 400 400.1 400.06667 483.88
7 2011 387.5 213.2 344.7333 495.2 620.9 567.83333 424.29
7 2012 421.5 271 381.1667 478.7 560.6 697.63333 439.32
7 2013 407.9 312.3 463.8 509.44 555.41 719.23333 424.94
7 2014 418.3 349.8 439.5667 569.84 572.55 722.3 411.5
7 2015 399.9 400 399.9667 595.03 458.3 667.2 395.06
7 2016 353 424.4 374.1333 635.28 420.51 698.56667 399.61
7 2017 415.9 529.7 429.3333 903.76 712.22 886.13333 361.76

What we see is that for the variables emp, gwage, and indp, the total for the base year 2015 is 400, so the average of the four quarters is 100. That makes sense to me.

We also see by that standard that the variables exp_index, imp_index, and indpturnover apparently already have 2010 as their base year, so we need do nothing to those values.

Finally, it is not clear to me if ex_rate has a base year - the most likely candidate seems to be 2016 with a total of 399.61.

I do not believe that what you propose to do is correct, given that your current base years are characterized by the sum of the four quarters being 100. If you use

new2011q1 = (2011q1/2010q1) x 100

as your guidance for the new value of 2011 using 2010 as a base year, then for 2010 applying the same logic gives

new2010q1 = (2010q1/2010q1) x 100 = 100

and each quarter of 2010 will be the same, although their sum will indeed be 400.

To change the base year for a variable to 2010, multiply the variable by 400 divided by the sum of its four quarters in 2010. (Example: emp = emp * 400/325.2) Then the sum of the four new values for 2010 will be 400 without them each being 100,

. drop industry _merge // not needed for this example

. generate year = yofd(dofq(qdate))

. foreach var of varlist emp gwage indp {
2. egen t2010 = total(cond(year==2010,`var',0))
3. replace `var' = `var' * 400 / t2010
4. drop t2010
5. }
(32 real changes made)
(32 real changes made)
(32 real changes made)

. list, clean noobs abbreviate(12)

id qdate emp gwage indp exp_index imp_index indpturnover ex_rate year
7 2010q1 82.902826 78.597343 96.11466 93 78.6 90.8 118.86 2010
7 2010q2 101.47601 93.107621 105.0274 100.7 85.7 102.93333 121.6 2010
7 2010q3 108.11808 101.57195 99.60811 97.4 116.8 99.633333 123.2 2010
7 2010q4 107.50307 126.7231 99.24981 108.9 119 106.7 120.22 2010
7 2011q1 103.81303 107.37606 97.72702 114.5 130.3 110.03333 111.68 2011
7 2011q2 119.31119 124.54656 104.8035 123 139.4 113.4 109.16 2011
7 2011q3 128.29028 136.63846 127.3766 130.1 166.7 165.6 99.89 2011
7 2011q4 125.21525 147.03749 133.2885 127.6 184.5 178.8 103.56 2011
7 2012q1 120.2952 144.37727 113.5371 121.8 155.2 177.9 108.3 2012
7 2012q2 134.19434 159.61307 132.3032 119.7 145.4 163.2 110.11 2012
7 2012q3 137.63837 178.71827 134.0052 120.3 143.7 169.03333 109.74 2012
7 2012q4 126.32226 172.67232 132.3032 116.9 116.3 187.5 111.17 2012
7 2013q1 115.74415 164.20799 128.0932 120.86 125.89 177.7 112.88 2013
7 2013q2 128.29028 178.47643 160.4747 122.72 137.52 168.13333 108.1 2013
7 2013q3 130.2583 195.88876 167.5512 130.79 141.68 181.96667 102.87 2013
7 2013q4 127.42927 216.68683 167.0586 135.07 150.32 191.43333 101.09 2013
7 2014q1 125.58425 192.01935 142.6492 146.85 168.23 193.03333 97.43 2014
7 2014q2 130.6273 208.70617 146.0531 135.61 147.25 167.93333 104.61 2014
7 2014q3 130.6273 219.58888 152.6369 143.26 130.9 174.3 103.6 2014
7 2014q4 127.67527 225.63483 149.2778 144.12 126.17 187.03333 105.86 2014
7 2015q1 119.31119 232.4063 121.5541 139.31 127.82 149.53333 104.75 2015
7 2015q2 125.95325 234.82468 135.9758 160.93 114.49 168.76667 99.19 2015
7 2015q3 127.06027 255.3809 142.8284 158.5 109.22 166.9 91.95 2015
7 2015q4 119.55719 244.74003 137.0507 136.29 106.77 182 99.17 2015
7 2016q1 102.82902 244.4982 109.9093 137.22 98.8 154.33333 102.64 2016
7 2016q2 106.02706 238.93592 125.0028 139.23 103.97 148.36667 101.86 2016
7 2016q3 109.71709 268.44016 126.0329 154.06 100.78 177.93333 101.52 2016
7 2016q4 115.62115 274.4861 141.7534 204.77 116.96 217.93333 93.59 2016
7 2017q1 117.46617 301.08828 129.6607 245.9 176.75 219.73333 90.89 2017
7 2017q2 127.67527 301.57196 136.0206 221.03 193.08 215.73333 92.86 2017
7 2017q3 132.34932 330.10884 153.7566 211.08 154.48 223.16667 91.67 2017
7 2017q4 134.07134 348.24669 157.4292 225.75 187.91 227.5 86.34 2017

. preserve

. collapse (sum) emp-ex_rate, by(id year)

. list, clean noobs abbreviate(12)

id year emp gwage indp exp_index imp_index indpturnover ex_rate
7 2010 399.99998 400.00001 400 400 400.1 400.06667 483.88
7 2011 476.62975 515.59857 463.1956 495.2 620.9 567.83333 424.29
7 2012 518.45017 655.38092 512.1487 478.7 560.6 697.63333 439.32
7 2013 501.722 755.26 623.1777 509.44 555.41 719.23333 424.94
7 2014 514.51413 845.94925 590.617 569.84 572.55 722.3 411.5
7 2015 491.8819 967.35191 537.4091 595.03 458.3 667.2 395.06
7 2016 434.19433 1026.3604 502.6985 635.28 420.51 698.56667 399.61
7 2017 511.5621 1281.0158 576.8671 903.76 712.22 886.13333 361.76

. restore

.

While I run the said code, it provides me the following result, which is different from your presented outcome. Does it any problem?


. list, clean noobs abbreviate(12)

id year emp gwage indp exp_index imp_index indpturnover ex_rate
7 2010 20.326588 17.087659 21.59111 400 400.1 400.06667 483.88
7 2011 24.220642 22.025931 25.00227 495.2 620.9 567.83333 424.29
7 2012 26.345808 27.997314 27.64464 478.7 560.6 697.63333 439.32
7 2013 25.495742 32.264063 33.63774 509.44 555.41 719.23333 424.94
7 2014 26.145793 36.13823 31.88019 569.84 572.55 722.3 411.50
7 2015 24.995703 41.324448 29.00814 595.03 458.3 667.2 395.06
7 2016 22.064224 43.845239 27.13454 635.28 420.51 698.56667 399.61
7 2017 25.995781 54.7239 31.138 903.76 712.22 886.13333 361.76
8 2010 18.220173 16.478124 20.41135 400 400.1 400 483.88
8 2011 20.451598 20.362622 26.57851 468.6 558.2 512.6 424.29
8 2012 22.826783 25.352549 27.93475 546.2 643.8 571.06667 439.32
8 2013 24.720681 30.75572 27.03542 630.95 636.47 662.73333 424.94
8 2014 25.026955 36.69611 24.84754 730.1 724.26 691.93333 411.50
8 2015 25.001953 41.314117 29.01298 891.56 841.89 824.13333 395.06
8 2016 24.770685 49.640993 28.55122 980.66 863.9 906.56667 399.61
8 2017 26.358309 59.114623 32.1848 1158.43 1005.83 1146.3 361.76
10 2010 18.188921 19.649775 22.79021 400 400 400 483.88
10 2011 19.676537 22.862751 25.16424 513.2 533.9 485.96667 424.29
10 2012 21.839206 26.75758 26.09016 535.3 561.5 521.36667 439.32
10 2013 22.983046 30.952012 27.72684 559.06 606.86 587.8 424.94
10 2014 24.245644 35.446045 28.88001 684.81 689.8 692.5 411.50
10 2015 24.995703 41.324448 29.01298 850.08 778.8 785.06667 395.06
10 2016 25.108212 49.475695 30.57955 835.48 831.71 819.9 399.61
10 2017 25.683257 55.00284 33.04544 941.71 991.09 926.56667 361.76
11 2010 20.695367 20.80686 25.29721 400 400 399.96667 483.88
11 2011 22.132979 24.112815 25.52687 461.7 483.8 451 424.29
11 2012 22.895539 28.327909 25.91126 520.9 506.5 508.26667 439.32
11 2013 24.589421 32.326049 25.40358 564.4 591.34 536.63333 424.94
11 2014 24.214392 36.479157 27.62047 647.37 694.12 616.86667 411.50
11 2015 25.001953 41.324448 29.01056 734.51 729.88 688.96667 395.06
11 2016 25.795765 48.752518 29.08308 799.8 720.6 721.9 399.61
11 2017 26.570826 53.349862 31.6892 941.11 781.21 844.33333 361.76
12 2010 44.572232 43.896895 21.41221 400 400 400 483.88
12 2011 34.765216 37.06803 20.81749 467.9 477.5 434.33333 424.29
12 2012 23.80811 33.276512 24.97567 516.9 502.3 529.46667 439.32
12 2013 23.208063 34.495583 24.25766 536.54 556.94 502.33333 424.94
12 2014 23.201813 38.049486 26.53983 612.04 650.74 557.56667 411.50
12 2015 25.008204 41.324448 29.01539 702.63 763.98 644.26667 395.06
12 2016 26.639581 44.454775 29.71648 811.92 822.91 765.73333 399.61
12 2017 27.539652 49.744304 29.13627 969.44 934.42 807.3 361.76
13 2010 18.795218 17.036004 21.52342 400 400 400 483.88
13 2011 20.139073 19.990702 23.17944 509.2 507.7 509.3 424.29
13 2012 22.289241 24.877318 24.97326 518.1 507.5 534.63333 439.32
13 2013 24.06438 30.10486 26.68246 562.87 524.62 593.4 424.94
13 2014 25.451988 35.611343 27.99519 638.59 595.56 666.66667 411.50
13 2015 24.995703 41.324448 29.01539 734.71 707.31 703.36667 395.06
13 2016 24.814439 50.849733 29.45539 772.2 750.3 770.93333 399.61
13 2017 25.233221 57.224029 31.30481 931.81 919.22 993.66667 361.76
14 2010 18.395187 17.728188 18.38544 400 400 400 483.88
14 2011 20.282835 21.323415 20.81266 497 486.4 496.6 424.29
14 2012 22.91429 27.046851 22.88691 505.8 557.4 539.06667 439.32
14 2013 24.32065 31.303269 23.18669 572.54 592.66 597.76667 424.94
14 2014 25.33948 36.076243 25.406 652.88 693.98 663.66667 411.50
14 2015 25.008204 41.324448 29.01539 741.8 846.72 707.1 395.06
14 2016 24.258145 50.756753 30.03802 781.23 943.31 753.56667 399.61
14 2017 24.601922 56.593832 31.49096 910.7 1141.1 912.63333 361.76
16 2010 18.213923 17.583553 21.44364 400 400.1 399.96667 483.88
16 2011 20.526604 21.488713 24.15612 477.8 493 497.2 424.29
16 2012 22.664271 26.406322 25.8194 524.6 524.6 567.86667 439.32
16 2013 23.345574 30.208172 27.52619 563.88 564.86 615.6 424.94
16 2014 24.658176 35.590681 30.09604 596.65 637.68 711.83333 411.50
16 2015 25.001953 41.324448 29.01056 682.13 743.82 808.96667 395.06
16 2016 24.695679 50.601787 31.35316 716.95 771.02 821.96667 399.61
16 2017 25.239472 57.399658 33.91093 854.96 951.62 930.4 361.76
17 2010 16.863817 17.635208 16.37888 399.9 400 400.03333 483.88
17 2011 18.251426 20.80686 18.57885 473.1 475.5 502.7 424.29
17 2012 19.932807 24.835993 19.5652 491.1 457.3 545.5 439.32
17 2013 21.664193 29.247378 22.03594 541.99 492.96 617.5 424.94
17 2014 23.67685 34.547239 25.46402 629.06 553.44 734.3 411.50
17 2015 25.001953 41.334779 29.01056 712.42 629.43 808.9 395.06
17 2016 25.33948 48.928146 30.43449 742.26 666.05 890.4 399.61
17 2017 25.908274 55.6537 33.24852 893.01 844.1 1181.3667 361.76
19 2010 21.564185 20.610568 21.53792 399.9 400 400 483.88
19 2011 23.895617 23.575598 23.92888 599 570.4 638.33333 424.29
19 2012 24.939448 27.491089 24.20689 667.8 618.8 735.13333 439.32
19 2013 25.08946 32.160752 23.11174 672.65 621.76 713.26667 424.94
19 2014 25.201969 33.648432 22.34055 714.88 668.35 693 411.50
19 2015 25.001953 41.324448 29.01539 525.47 490.28 647.73333 395.06
19 2016 25.514493 45.973448 28.89693 479.04 424.25 623.76667 399.61
19 2017 26.652082 55.1888 29.08067 703.09 636.78 956.4 361.76
20 2010 20.126572 20.259311 23.34866 400 400 400.03333 483.88
20 2011 21.520431 23.358644 26.7018 502.5 524.7 517.56667 424.29
20 2012 22.714275 26.767911 26.49147 536.4 527 565.7 439.32
20 2013 23.058051 30.094529 27.23124 572.21 551.24 600.9 424.94
20 2014 24.220642 35.02247 28.48837 645.97 624.08 681.66667 411.50
20 2015 25.008204 41.314117 29.01298 735.96 689.6 718.63333 395.06
20 2016 25.314478 47.895035 29.2644 755.96 671.32 761.4 399.61
20 2017 25.908274 54.599927 31.1936 915.06 880.89 981.66667 361.76
21 2010 23.151809 29.598636 17.5393 400.1 400 400.03333 483.88
21 2011 23.320572 32.047109 19.5096 421 420.1 395.36667 424.29
21 2012 23.589343 33.224856 22.45901 751.9 372.1 436.1 439.32
21 2013 24.383155 33.648432 20.9432 776.36 419.78 421.53333 424.94
21 2014 24.608173 35.136112 24.277 835.81 464.92 467.33333 411.50
21 2015 25.001953 41.324448 29.01056 995.24 545.8 570 395.06
21 2016 25.395734 44.806033 31.06547 1146.15 594.5 661.96667 399.61
21 2017 27.333385 52.089467 33.40082 1644.11 713.99 800.86667 361.76
22 2010 17.120088 17.314944 20.07047 400 400 400.03333 483.88
22 2011 19.220252 20.910171 23.96514 500.4 497 530.33333 424.29
22 2012 20.98914 25.207913 24.63722 526.9 526.9 572.26667 439.32
22 2013 22.120478 29.040756 26.41895 570.08 555.63 647.2 424.94
22 2014 23.389327 33.772405 27.7631 644.92 620.4 748.46667 411.50
22 2015 25.008204 41.324448 29.01056 732.81 710.62 803.1 395.06
22 2016 25.489491 50.612118 30.29911 774.78 765.36 858 399.61
22 2017 26.383311 58.226147 33.71269 933.41 932.29 1108.2667 361.76
23 2010 18.245175 19.071233 20.77881 399.9 400 399.93333 483.88
23 2011 20.526604 22.697453 23.7669 462.4 484.5 485 424.29
23 2012 22.089226 26.902216 24.90315 480.3 518.2 525.43333 439.32
23 2013 23.370576 30.745389 27.42707 522.09 546 597.93333 424.94
23 2014 24.433159 35.322072 28.42793 602.46 627.45 679.1 411.50
23 2015 25.008204 41.324448 29.01056 696 750.08 733.16667 395.06
23 2016 25.189468 49.1451 29.7334 724.42 819.54 770.8 399.61
23 2017 25.545746 54.734231 32.52809 838.32 1009.28 912.53333 361.76
24 2010 19.364013 19.546464 21.99967 400 400 400.03333 483.88
24 2011 21.095398 23.813213 25.59215 536.8 519.9 569.26667 424.29
24 2012 22.301742 27.398109 27.02333 559 512.1 598.56667 439.32
24 2013 23.17056 31.654527 28.62375 537.87 496.85 624.43333 424.94
24 2014 24.264396 35.776641 29.2644 592.16 536.48 707.93333 411.50
24 2015 25.008204 41.324448 29.01298 616.57 554.33 700.93333 395.06
24 2016 25.026955 46.748282 28.69628 677.79 576.02 735.4 399.61
24 2017 25.758262 53.711451 30.72218 890.75 814.42 1087.1 361.76
25 2010 16.670052 16.147528 16.10086 400.1 400 399.93333 483.88
25 2011 19.389015 20.238648 20.53948 481.6 475 535.66667 424.29
25 2012 21.020392 24.846324 22.40582 491.6 517 583.53333 439.32
25 2013 22.876787 29.670954 25.48819 527.34 575.22 650.26667 424.94
25 2014 24.145636 34.598894 27.39564 603.03 654.94 743.4 411.50
25 2015 24.995703 41.324448 29.01056 696.72 789.53 823.53333 395.06
25 2016 25.358231 50.126555 30.63031 747.84 883.5 847.16667 399.61
25 2017 25.970779 57.224029 34.36301 900.57 1099.4 1073.8333 361.76
26 2010 17.401359 16.953355 16.96634 400 400.1 400 483.88
26 2011 18.976483 20.724211 17.65051 395.5 468.2 503.63333 424.29
26 2012 20.470349 25.125264 21.2454 402 531.9 543.8 439.32
26 2013 21.75795 30.10486 24.10052 481.97 581.68 529.5 424.94
26 2014 23.964372 35.869621 27.73893 532.14 690.93 635.06667 411.50
26 2015 25.001953 41.314117 29.01056 686.53 851.53 735.46667 395.06
26 2016 24.970701 48.783511 33.42742 677.94 919.1 838.6 399.61
26 2017 26.095789 58.401776 37.99658 867.92 1086.4 1006.9667 361.76
27 2010 17.220095 17.893486 18.39269 400.1 400 400 483.88
27 2011 19.289007 21.354409 22.34538 479.6 448.1 511.5 424.29
27 2012 21.020392 25.238907 22.88691 488.7 453.7 556.83333 439.32
27 2013 22.439253 29.701947 25.11347 525.55 513.57 649.66667 424.94
27 2014 23.67685 34.041014 27.03784 600.97 588.19 718.86667 411.50
27 2015 25.001953 41.314117 29.00814 671.67 700.43 795.5 395.06
27 2016 25.670756 48.876491 28.70595 715.01 762.54 882.2 399.61
27 2017 26.433315 54.434629 31.35558 871.54 911.13 1118.8 361.76
28 2010 16.982577 16.209515 15.789 400 400.1 400.03333 483.88
28 2011 19.501524 20.507257 21.37111 481.6 462.7 563.23333 424.29
28 2012 21.226658 25.218244 22.63065 503.1 488.6 623.33333 439.32
28 2013 22.02047 28.823803 24.40997 547.22 535.74 713.4 424.94
28 2014 23.670599 33.875716 27.20223 620.43 608.38 817.96667 411.50
28 2015 25.001953 41.324448 29.01056 722.75 724.52 887.76667 395.06
28 2016 25.645754 50.043907 29.27165 783.84 793.46 979.46667 399.61
28 2017 26.402063 57.678598 32.01315 939.05 958.16 1232 361.76
29 2010 17.882647 18.492691 18.24281 400 400 400.03333 483.88
29 2011 20.107821 21.788315 22.82889 468.9 469.3 534.83333 424.29
29 2012 21.101649 25.073609 21.10518 477.2 479.9 515.86667 439.32
29 2013 22.207985 29.040756 22.94977 518.45 524.62 611.6 424.94
29 2014 23.433081 33.028565 24.37612 586.78 595.62 686.36667 411.50
29 2015 24.995703 41.334779 29.01298 652.36 686.23 855.46667 395.06
29 2016 26.445816 49.51702 30.93493 725.4 773.77 990.13333 399.61
29 2017 27.627158 56.800454 36.24627 907.98 939.31 1358.6 361.76

[William Lisowski]

Yes, that is certainly a problem.

I see that I made a mistake that I overlooked because I only had one ID to test with. The corrected code is as follows.

generate year = yofd(dofq(qdate))
foreach var of varlist emp gwage indp {
    bysort id: egen t2010 = total(cond(year==2010,`var',0))
    replace `var' = `var' * 400 / t2010
    drop t2010
}
preserve
collapse (sum) emp-ex_rate, by(id year)
list, clean noobs abbreviate(12)
restore

[Muhammad Salahuddin]

wow it's working, Thanks a lot.