Expand a dataset with conditions

[Miguel Vicente Vaz]

Dear fellow Stata users,

I am working with an unbalanced panel dataset, with companies’ id and monthly dates.
I would like to expand my dataset in a way such that: the key variable is continued until the next observation recorded or throughout the following 12 months, if the next observation I have is after 12 months. And, when there are no more entries for a company, I would like to continue the variable also for the next 12 months.

For example, if for one company I have these (and only these) values,

* Example generated by -dataex-. For more info, type help dataex
clear
input int id float date byte keyvar
10001 663 0
10001 672 4
10001 701 2
end
format %tm date

I would like to make my dataset as follows,

* Example generated by -dataex-. For more info, type help dataex
clear
input int id float date byte keyvar
10001 663 0
10001 664 0
10001 665 0
10001 666 0
10001 667 0
10001 668 0
10001 669 0
10001 670 0
10001 671 0
10001 672 4
10001 673 4
10001 674 4
10001 675 4
10001 676 4
10001 677 4
10001 678 4
10001 679 4
10001 680 4
10001 681 4
10001 682 4
10001 683 4
10001 684 4
10001 701 2
10001 702 2
10001 703 2
10001 704 2
10001 705 2
10001 706 2
10001 707 2
10001 708 2
10001 709 2
10001 710 2
10001 711 2
10001 712 2
10001 713 2
end
format %tm date

I hope I made myself clear with this example.

Thanks in advance.

Kindly,
Miguel

[Nick Cox]

This may help. The reason for the variable block is just to see the results more clearly. Why interpolation is valid for 12 months but not 13 is an interesting question.

* Example generated by -dataex-. For more info, type help dataex
clear
input int id float date byte keyvar
10001 663 0
10001 672 4
10001 701 2
end
format %tm date

bysort id (date) : gen gap = date[_n+1] - date 
gen toexpand = min(13, gap)

expand toexpand, gen(new)

bysort id (date new) : replace date = date[_n-1] + 1 if new 

bysort id (date): gen block = sum(new == 0)

list, sepby(id block)

     +--------------------------------------------------------------+
     |    id      date   keyvar   gap   toexpand        new   block |
     |--------------------------------------------------------------|
  1. | 10001    2015m4        0     9          9   Original       1 |
  2. | 10001    2015m5        0     9          9   Duplicat       1 |
  3. | 10001    2015m6        0     9          9   Duplicat       1 |
  4. | 10001    2015m7        0     9          9   Duplicat       1 |
  5. | 10001    2015m8        0     9          9   Duplicat       1 |
  6. | 10001    2015m9        0     9          9   Duplicat       1 |
  7. | 10001   2015m10        0     9          9   Duplicat       1 |
  8. | 10001   2015m11        0     9          9   Duplicat       1 |
  9. | 10001   2015m12        0     9          9   Duplicat       1 |
     |--------------------------------------------------------------|
 10. | 10001    2016m1        4    29         13   Original       2 |
 11. | 10001    2016m2        4    29         13   Duplicat       2 |
 12. | 10001    2016m3        4    29         13   Duplicat       2 |
 13. | 10001    2016m4        4    29         13   Duplicat       2 |
 14. | 10001    2016m5        4    29         13   Duplicat       2 |
 15. | 10001    2016m6        4    29         13   Duplicat       2 |
 16. | 10001    2016m7        4    29         13   Duplicat       2 |
 17. | 10001    2016m8        4    29         13   Duplicat       2 |
 18. | 10001    2016m9        4    29         13   Duplicat       2 |
 19. | 10001   2016m10        4    29         13   Duplicat       2 |
 20. | 10001   2016m11        4    29         13   Duplicat       2 |
 21. | 10001   2016m12        4    29         13   Duplicat       2 |
 22. | 10001    2017m1        4    29         13   Duplicat       2 |
     |--------------------------------------------------------------|
 23. | 10001    2018m6        2     .         13   Original       3 |
 24. | 10001    2018m7        2     .         13   Duplicat       3 |
 25. | 10001    2018m8        2     .         13   Duplicat       3 |
 26. | 10001    2018m9        2     .         13   Duplicat       3 |
 27. | 10001   2018m10        2     .         13   Duplicat       3 |
 28. | 10001   2018m11        2     .         13   Duplicat       3 |
 29. | 10001   2018m12        2     .         13   Duplicat       3 |
 30. | 10001    2019m1        2     .         13   Duplicat       3 |
 31. | 10001    2019m2        2     .         13   Duplicat       3 |
 32. | 10001    2019m3        2     .         13   Duplicat       3 |
 33. | 10001    2019m4        2     .         13   Duplicat       3 |
 34. | 10001    2019m5        2     .         13   Duplicat       3 |
 35. | 10001    2019m6        2     .         13   Duplicat       3 |
     +--------------------------------------------------------------+

[Miguel Vicente Vaz]

Works perfectly, thanks a lot!