Expand a panel dataset by adding more years, filling in values with the data from the most recent available year

[Pantelis Kazakis]

Hi all,

Assume we have a panel dataset with the following variables: (i) FIPS code identifying counties, (ii) year, and (iii) broadband. The panel data covers the years 2008, 2009, and 2010. I would like to expand the dataset by adding the years 2011 and 2012, where the broadband variable takes the value from the most recent available year.

I am providing a minimal example below.

input int(fips year) float broadband
1001 2008 7.333333
1001 2009 6.25
1001 2010 5.916667
1003 2008 5.806229
1003 2009 5.741742
1003 2010 6.031991
end

What I want to achieve is something that looks like this:

fips year broadband 1001 2008 7.333333 1001 2009 6.25 1001 2010 5.916667 1001 2011 5.916667 1001 2012 5.916667 1003 2008 5.806229 1003 2009 5.741742 1003 2010 6.031991 1003 2011 6.031991 1003 2012 6.031991

[Nick Cox]

This works for your data example:

clear 
input int(fips year) float broadband
1001 2008 7.333333
1001 2009 6.25
1001 2010 5.916667
1003 2008 5.806229
1003 2009 5.741742
1003 2010 6.031991
end

expand 3 if year == 2010 

bysort fips (year) : replace year = 2007 + _n 

list, sepby(fips)

For discussions of principle, see https://journals.sagepub.com/doi/pdf...867X0500500117 and https://journals.sagepub.com/doi/pdf...6867X231196519

[Pantelis Kazakis]

Thanks Nick!