Foreach loop to populate missing observations of a variable from one year to multiple years

I am working on several variables that i reshaped data from wide to long format and created a year variable for panel-data. Unfortunately, when reshaping it, variables that observations represented 3-year or 5-year estimates were reshaped and populated in only one year. I was able to figure out that creating a foreach loop is the best approach to copying the observation values of a variable from one year to multiple years. In other words, a variable in wide-format vet12 represents the number of veterans 2012-2016. When reshaped, the values populated only veterans in 2012. I would like to use a loop to populate the values in 2012 to 2013-2016. Here is what I have so far but when I execute the command, it populates the variables in the range 2012 to 2016 but with the value '1' instead of the value of the observations from the year 2012.

Code:

foreach var of varlist vetedu_hs vetedu_hsplus vetedu_college {
    by n_county (`var'), sort: assert `var' == `var'[1] | missing(`var')
}
   by n_county (vetedu_hs), sort: replace vetedu_hs = vetedu_hs[1] | missing(vetedu_hs) if inrange(year, 12, 16)
   by n_county (vetedu_hsplus), sort: replace vetedu_hsplus = vetedu_hsplus[1] if inrange(year, 12, 16)
   by n_county (vetedu_college), sort: replace vetedu_college = vetedu_college[1] if inrange(year, 12, 16)

I provided some data to maybe get a better understanding of the data. Any help would be appreciated.

Code:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float n_county byte year long vetedu_hsplus int vetedu_hs long vetedu_college
 517 12  3885  424   797
2600 12  2066  258   371
 583 12  1215   91   213
3083 12  1226  244   112
2039 12    40    6     6
 182 12  1587  276   212
1793 12 19101  978  8185
 333 12 18019 1614  4002
 976 12   280   10    82
2321 12   278   75   122
1891 12  4414  363   753
  59 12  5776  528  1101
 370 12 30242  829 12283
3208 12  1979   97   790
 713 12  1591   98   204
1120 12  1362  270   176
2266 12  6021  706   818
2612 12   197    9    31
1289 12  2131  175   434
2917 12  6221  557  1772
3199 12  2032  172   573
1258 12  2547  107   489
1717 12    56    5    17
3179 12  3274  256   799
2620 12 42719 1024 11010
 988 12   245   11    27
2409 12 27305 1349 10111
1194 12  3659  152  1350
2083 12  1640  250   248
2166 12   205   10    51
1396 12  1704  249   230
  65 12   830   39   133
3092 12   737  159   142
2881 12 41611 1913 14868
1282 12  6664  484  1004
1751 12    41    6     0
 657 12 20494 1220  4835
2112 12  2187  387   319
2000 12   476   54   124
1280 12  5566  435   839
1489 12  1382  144   364
1383 12   911  102   134
1567 12  1190  199   156
1177 12  3185  430   584
2431 12 20640 1291  6352
 640 12   556   41    87
1826 12 12574  491  4479
2650 12   195    8    52
2834 12  1315  184   147
 234 12 10887  360  4542
2098 12 15938 1429  3034
2793 12  3822  418   569
 708 12  2877  234  1000
2345 12   363   93    48
  94 12   769   24   117
3015 12  6965  460  2323
2624 12  1411  131   240
 777 12  1529  160   195
1896 12  9209  664  2094
2886 12  3665  213   726
  43 12   376   58    84
3114 12  1630  252   229
1562 12  1037   84   205
2479 12    82    9    17
2594 12 10572  866  3009
2094 12  2859  358   331
2468 12   912   62   200
2844 12   556  111   114
2155 12  4891  562   818
2985 12   790  105   307
 149 12   952  198    87
3182 12   487   47    87
 946 12   250   26    39
 864 12  2174  207   431
 890 12  1117  149   322
1125 12   859  103   111
1453 12  1510  220   192
1721 12   268   29    38
 268 12  1836  136  1056
1538 12 15109 1569  2665
 289 12    95    0    46
 699 12 29503 2140  7770
2627 12  8061  382  3555
 461 12   425   37    92
3091 12   419  131    76
1593 12  3074  348   665
 307 12   199   17    44
1535 12   728   80    84
 937 12  1356  115   179
 116 12  1022  199   216
1286 12 47446 3962 10183
 563 12  1370    0   641
 449 12  9859  341  4536
2669 12   137   29    25
1642 12   702   50   148
 152 12   405   39    52
1057 12  9675  961  2530
1765 12  5532  606   951
2933 12 24794 1143  9208
2165 12   126   20    20
end
label var n_county "group(statename countyname)" 
label var year "Reshaped Year variable panel data" 
label var vetedu_hsplus "Veterans 25+ w/HS Diploma+ 2012-16" 
label var vetedu_hs "Veterans 25+ w/< HS Diploma 2012-16" 
label var vetedu_college "Veterans 25+ w/4+ Yrs College 2012-16"

Any help would be appreciated.

Thanks,

Rene
Using Stata 12.1 on Mac OSx

Share on FacebookPlus on Google+