Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • Changing data structure (Expanding observations) with previous value

    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.
    Last edited by Nathan Han; 24 Nov 2020, 21:44.

  • #2
    Code:
    * 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.:

    Code:
    . 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 |
         +-------------------+
    Last edited by Andrew Musau; 25 Nov 2020, 07:37.

    Comment


    • #3
      Thanks Andrew!

      Comment

      Working...
      X