Using -expand- to duplicate multiple rows

[Kelly Marsh]

I have data that look like the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(sid startyr endyr cost)
1001 2006 9999 545
1001 2010 2011 690
1001 2011 2012 701
end

I would like to expand the first row where the start year is 2006. The end year 9999 represents whatever is the year before the following row, so in this case 2009 (because the following row is 2010). I would also like the rows that I expand to have a start year that is the year following the previous row. For example, I am looking for something like this:

* Example generated by -dataex-. To install: ssc install dataex
clear
input int(sid startyr endyr cost)
1001 2006 2007 545
1001 2007 2008 545
1001 2008 2009 545
1001 2009 2010 545
1001 2010 2011 690
1001 2011 2012 701
end

Any suggestions greatly appreciated.

[Ali Atia]

bys sid (startyr): gen expand = startyr[_n+1]-startyr
expand expand,gen(expand2)
bys sid (startyr expand2): replace startyr = startyr[_n-1]+1 if expand2
replace endyr = startyr+1
drop expand*

[Clyde Schechter]

Thank you for using -dataex- on your first post!

by sid (startyr), sort: replace endyr = startyr[_n+1] if endyr == 9999

expand endyr-startyr
by sid startyr, sort: replace startyr = startyr[1] + _n - 1
replace endyr = startyr + 1

list, noobs clean

Added: Crossed with #2.