Expand or Filling in rows

[Esther Chau]

Hello everyone,

Thank you in advance for your help. I have been trying to adapt codes I see on the forum to expand my dataset.
I need to (1) add rows for skipped years in between, (2) add rows for skipped year upfront (should start with year 0), and (3) input values as zeros for those newly added rows.
The problem is, the gap between row varies to a great deal and my dataset is considerably large. I couldn't get codes that consistently and efficiently work for all situations.
Would anyone please suggest a set of codes that I can do this accurately?

An illustrative example of my data is like this:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(ID year value)
1  1  6
1  2 22
1  3 35
1  4  2
1  6  1
1  7  8
2  2 76
2  3  3
2  4 24
2  6  5
2  7 16
2  8  9
3  4 56
3  5 45
3 13 22
3 14 12
3 16  7
3 19  4
3 20  3
3 21  1
3 23  1
end

How I want it to look:

* Example generated by -dataex-. For more info, type help dataex
clear
input byte(ID year value)
1  0  0
1  1  6
1  2 22
1  3 35
1  4  2
1  5  0
1  6  1
1  7  8
2  0  0
2  1  0
2  2 76
2  3  3
2  4 24
2  5  0
2  6  5
2  7 16
2  8  9
3  0  0
3  1  0
3  2  0
3  3  0
3  4 56
3  5 45
3  6  0
3  7  0
3  8  0
3  9  0
3 10  0
3 11  0
3 12  0
3 13 22
3 14 12
3 15  0
3 16  7
3 17  0
3 18  0
3 19  4
3 20  3
3 21  1
3 22  0
3 23  1
end

Thank you very much in advance. I really appreciate all the help.

[Nick Cox]

Here is one way to do it.

* example generated by -dataex-. For more info, type help dataex
clear
input byte(ID year value)
1  1  6
1  2 22
1  3 35
1  4  2
1  6  1
1  7  8
2  2 76
2  3  3
2  4 24
2  6  5
2  7 16
2  8  9
3  4 56
3  5 45
3 13 22
3 14 12
3 16  7
3 19  4
3 20  3
3 21  1
3 23  1
end

save original_dataset  

collapse (max) year, by(ID) 
replace year = year + 1 
expand year 
bysort ID : replace year = year - _n 

merge 1:1 ID year using original_dataset 

replace value = 0 if value == . 

sort ID year
list, sepby(ID)