data structure transformation

[Sang-Bum Park]

Hi

How can the left structure be transformed into the right one?

The 'school' indicates school id.
The 'open' means the year of establishment.

The variable name of 'open' in the output table (right) can be different.

[Carlo Lazzaro]

Sang-Bum:
the following toy-example may be helpful:

. set obs 3
Number of observations (_N) was 0, now 3.

. g schools=_n

. g year=2017

. expand 5
(12 observations created)

. bysort schools year: replace year=2017+(_n-1)
(12 real changes made)

. list

     +----------------+
     | schools   year |
     |----------------|
  1. |       1   2017 |
  2. |       1   2018 |
  3. |       1   2019 |
  4. |       1   2020 |
  5. |       1   2021 |
     |----------------|
  6. |       2   2017 |
  7. |       2   2018 |
  8. |       2   2019 |
  9. |       2   2020 |
 10. |       2   2021 |
     |----------------|
 11. |       3   2017 |
 12. |       3   2018 |
 13. |       3   2019 |
 14. |       3   2020 |
 15. |       3   2021 |
     +----------------+

.

Second thought:

. set obs 3
Number of observations (_N) was 0, now 3.

. g schools=_n

. expand 5 if schools==1

. expand 4 if schools==2

. bysort schools : g year=2017+(_n-1) if schools==1

. bysort schools year: replace year=2018+(_n-1) if schools==2

. bysort schools year: replace year=2021+(_n-1) if schools==3
(1 real change made)

. list

     +----------------+
     | schools   year |
     |----------------|
  1. |       1   2017 |
  2. |       1   2018 |
  3. |       1   2019 |
  4. |       1   2020 |
  5. |       1   2021 |
     |----------------|
  6. |       2   2018 |
  7. |       2   2019 |
  8. |       2   2020 |
  9. |       2   2021 |
 10. |       3   2021 |
     +----------------+

.

How -open- should be coded it's up to you.

[Nick Cox]

Sang-Bum Park Please read and act on FAQ Advice #12 and use dataex

* Example generated by -dataex-. For more info, type help dataex
clear
input float(school open)
1 2017
2 2018
3 2021
end

gen toexpand = 2021 - open + 1 
expand toexpand 
bysort school: replace open = open[_n-1] + 1 if _n > 1

[Sang-Bum Park]

Thank you, Carlo Lazzaro Nick Cox.