Changing data structure (Expanding observations) with previous value

[Nathan Han]

Dear all,

Thanks in advance for any suggestions and supports!
I am currently working on a dataset with three variables that looks like the following

group	year	id
1	2000	1
1	2000	2
1	2001	5
2	2007	1
2	2007	3
2	2008	4
2	2010	5

This dataset contains random records of ids in different groups at different years. I am hoping to expand the dataset to track the historical ids (of all previous years) of groups in each year.
The new data structure should ideally look like the following

group	year	id
1	2000	1
1	2000	2
1	2001	1
1	2001	2
1	2001	5
2	2007	1
2	2007	3
2	2008	1
2	2008	3
2	2008	4
2	2010	1
2	2010	3
2	2010	4
2	2010	5

I have tried to use suggestions from this link: https://stats.idre.ucla.edu/stata/fa...time-variable/.
However, my dataset is not exactly a panel dataset. Does anyone have suggestions for this issue? I would appreciate any advice.
This question is cross-posted in the Mata forum too.

[Andrew Musau]

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte group int year byte id
1 2000 1
1 2000 2
1 2001 5
2 2007 1
2 2007 3
2 2008 4
2 2010 5
end

preserve
contract group year
tempfile present
save `present'
restore
bys group (year): gen toexpand=year[_N]-year+1
expand toexpand
bys group id year: replace year= year+_n-1 if _n>1
merge m:1 group year using `present', keep(match)
drop toexpand _freq _merge
sort group year id

Res.:

. l, sepby(group)

     +-------------------+
     | group   year   id |
     |-------------------|
  1. |     1   2000    2 |
  2. |     1   2000    1 |
  3. |     1   2001    2 |
  4. |     1   2001    1 |
  5. |     1   2001    5 |
     |-------------------|
  6. |     2   2007    1 |
  7. |     2   2007    3 |
  8. |     2   2008    3 |
  9. |     2   2008    4 |
 10. |     2   2008    1 |
 11. |     2   2010    3 |
 12. |     2   2010    4 |
 13. |     2   2010    5 |
 14. |     2   2010    1 |
     +-------------------+

[Nathan Han]

Thanks Andrew!