Replacing with lagged value.

[Sumedha Gupta]

Dear All,

I have population data by age brackets(7 age categories *agecats* : 0-14, 15-24, 25-34, 35-44, 45-54, 55-64, 65+) for the 50 US states. Some values are missing. So I want to replace the missings with lagged / forward values. My data looks as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str5 state byte stateFIPS float(qtr agecats popover65)
"AK" 2  1 1     .
"AK" 2  1 2 58625
"AK" 2  1 3 58625
"AK" 2  1 4 58625
"AK" 2  1 5 58625
"AK" 2  1 6     .
"AK" 2  1 7 58625
"AK" 2  2 1     .
"AK" 2  2 2 58625
"AK" 2  2 3 58625
"AK" 2  2 4 58625
"AK" 2  2 5 58625
"AK" 2  2 6     .
"AK" 2  2 7 58625
"AK" 2  3 1     .
"AK" 2  3 2 58625
"AK" 2  3 3     .
"AK" 2  3 4 58625
"AK" 2  3 5     .
"AK" 2  3 6 58625
"AK" 2  3 7     .
"AK" 2  4 1     .
"AK" 2  4 2 58625
"AK" 2  4 3 58625
"AK" 2  4 4 58625
"AK" 2  4 5 58625
"AK" 2  4 6 58625
"AK" 2  4 7 58625
"AK" 2  5 1     .
"AK" 2  5 2 62488
"AK" 2  5 3 62488
"AK" 2  5 4 62488
"AK" 2  5 5 62488
"AK" 2  5 6 62488
"AK" 2  5 7     .
"AK" 2  6 1     .
"AK" 2  6 2     .
"AK" 2  6 3 62488
"AK" 2  6 4 62488
"AK" 2  6 5 62488
"AK" 2  6 6 62488
"AK" 2  6 7     .
"AK" 2  7 1     .
"AK" 2  7 2     .
"AK" 2  7 3 62488
"AK" 2  7 4     .
"AK" 2  7 5 62488
"AK" 2  7 6     .
"AK" 2  7 7 62488
"AK" 2  8 1     .
"AK" 2  8 2     .
"AK" 2  8 3 62488
"AK" 2  8 4 62488
"AK" 2  8 5 62488
"AK" 2  8 6 62488
"AK" 2  8 7     .
"AK" 2  9 1     .
"AK" 2  9 2     .
"AK" 2  9 3 65955
"AK" 2  9 4     .
"AK" 2  9 5 65955
"AK" 2  9 6     .
"AK" 2  9 7     .
"AK" 2 10 1     .
"AK" 2 10 2 65955
"AK" 2 10 3 65955
"AK" 2 10 4 65955
"AK" 2 10 5 65955
"AK" 2 10 6 65955
"AK" 2 10 7     .
"AK" 2 11 1     .
"AK" 2 11 2 65955
"AK" 2 11 3 65955
"AK" 2 11 4 65955
"AK" 2 11 5 65955
"AK" 2 11 6     .
"AK" 2 11 7 65955
"AK" 2 12 1     .
"AK" 2 12 2 65955
"AK" 2 12 3 65955
"AK" 2 12 4 65955
"AK" 2 12 5 65955
"AK" 2 12 6 65955
"AK" 2 12 7     .
"AK" 2 13 1     .
"AK" 2 13 2 69365
"AK" 2 13 3     .
"AK" 2 13 4     .
"AK" 2 13 5 69365
"AK" 2 13 6     .
"AK" 2 13 7     .
"AK" 2 14 1 69365
"AK" 2 14 2 69365
"AK" 2 14 3 69365
"AK" 2 14 4 69365
"AK" 2 14 5     .
"AK" 2 14 6 69365
"AK" 2 14 7 69365
"AK" 2 15 1     .
"AK" 2 15 2 69365
end

I was trying to do the following:

tsset stateFIPS qtr agecats
replace popover65 =L1.popover65 if popover65==. & L1.popover65~=.
replace popover65 =F1.popover65 if popover65==. & F1.popover65~=.

But I get the error:

too many variables specified
r(103);

Is there a solution?

Thank you in advance,
Sincerely,
Sumedha.

[William Lisowski]

For the purposes of what you are trying to do, your "panel" is really the combination of stateFIPS and agecats - if a value is missing, you want to fill it in with the previous or following value for the same state and age category.

The following changes will eliminate the error from your tsset command and do the commands that follow it.

egen stateage = group(stateFIPS agecats)
tsset stateage qtr

This may not totally accomplish what you want, because if you have a missing value in the first thirteen quarters, as you do for agecats==1, only the thirteenth quarter will be filled in by the second replace. You would have to run the second replace repeatedly to have it carry values all the way back to the first quarter. But that may not really be what you would want to do.