wide to long format

[River Huang]

Dear All, I have this data set

* Example generated by -dataex-. For more info, type help dataex
clear
input float(t a b c d e)
2007  .5353563 .34855065  .7346603   1.271293    .677357
2008  .9427623  .8232493    .60687 -1.6404753  .08203877
2009  .2045144  .3962567  .6496394  -.4025347 -.02510653
2010  .6165885  .8905014  .7293703  -.6044371  1.3021728
2012   .895983  .2597431  .4194049     .85118    .929715
2013  .3532184  .6821833  .8635429   .4328441   .6115245
2014  .8658451   .773515  .1496965  1.1787486  -1.183833
2015 .44023645 .53001946  .4219775  .14264178 -1.7453043
2016  .9339633  .8974178  .8077219  -.8194401   .4568353
2017  .2337515 .18127295 .22307622 -1.4533758 -1.4463307
2018  .6788335   .554011   .633172     1.5417 -1.7881067
2019  .8262904  .8612276  .7002477  -.5125332  -.8761352
2020   .543996  .3106355  .4897899  1.7953657  .21474987
end

I use the code

xpose, clear v
reshape long v, i(_varname) j(year)
ren (_varname v) (v x)
drop if v == "t"
replace year = year+2006

and obtain

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 v int year float x
"a" 2007   .5353563
"a" 2008   .9427623
"a" 2009   .2045144
"a" 2010   .6165885
"a" 2011    .895983
"a" 2012   .3532184
"a" 2013   .8658451
"a" 2014  .44023645
"a" 2015   .9339633
"a" 2016   .2337515
"a" 2017   .6788335
"a" 2018   .8262904
"a" 2019    .543996
"b" 2007  .34855065
"b" 2008   .8232493
"b" 2009   .3962567
"b" 2010   .8905014
"b" 2011   .2597431
"b" 2012   .6821833
"b" 2013    .773515
"b" 2014  .53001946
"b" 2015   .8974178
"b" 2016  .18127295
"b" 2017    .554011
"b" 2018   .8612276
"b" 2019   .3106355
"c" 2007   .7346603
"c" 2008     .60687
"c" 2009   .6496394
"c" 2010   .7293703
"c" 2011   .4194049
"c" 2012   .8635429
"c" 2013   .1496965
"c" 2014   .4219775
"c" 2015   .8077219
"c" 2016  .22307622
"c" 2017    .633172
"c" 2018   .7002477
"c" 2019   .4897899
"d" 2007   1.271293
"d" 2008 -1.6404753
"d" 2009  -.4025347
"d" 2010  -.6044371
"d" 2011     .85118
"d" 2012   .4328441
"d" 2013  1.1787486
"d" 2014  .14264178
"d" 2015  -.8194401
"d" 2016 -1.4533758
"d" 2017     1.5417
"d" 2018  -.5125332
"d" 2019  1.7953657
"e" 2007    .677357
"e" 2008  .08203877
"e" 2009 -.02510653
"e" 2010  1.3021728
"e" 2011    .929715
"e" 2012   .6115245
"e" 2013  -1.183833
"e" 2014 -1.7453043
"e" 2015   .4568353
"e" 2016 -1.4463307
"e" 2017 -1.7881067
"e" 2018  -.8761352
"e" 2019  .21474987
end

My first question is: Is there a more concise way to do so?
My second question is: How to construct pairs among "v"'s elements (ab, ac, ad, ae, bc, bd, be (with different years), and so on) in long format?
Any suggestions are highly appreciated.

[Nick Cox]

Q1

rename (a-e) (wanted=)
reshape long wanted, i(t) j(which) string

Q2

Isn't this what you started from?

[River Huang]

Dear Nick, Plese forgive my poor English. I'd like to have this data

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 v int year float x
"a" 2007   .5353563
"a" 2008   .9427623
"a" 2009   .2045144
"b" 2007  .34855065
"b" 2008   .8232493
"b" 2009   .3962567
"c" 2007   .7346603
"c" 2008     .60687
"c" 2009   .6496394
"d" 2007   1.271293
"d" 2008 -1.6404753
"d" 2009  -.4025347
end

to become like

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 v int year float x str8 v1 int year1 float x1
"a" 2007  .5353563 "b" 2007  .34855065
"a" 2008  .9427623 "b" 2008   .8232493
"a" 2009  .2045144 "b" 2009   .3962567
"a" 2007  .5353563 "c" 2007   .7346603
"a" 2008  .9427623 "c" 2008     .60687
"a" 2009  .2045144 "c" 2009   .6496394
"a" 2007  .5353563 "d" 2007   1.271293
"a" 2008  .9427623 "d" 2008 -1.6404753
"a" 2009  .2045144 "d" 2009  -.4025347
"b" 2007 .34855065 "c" 2007   .7346603
"b" 2008  .8232493 "c" 2008     .60687
"b" 2009  .3962567 "c" 2009   .6496394
"b" 2007 .34855065 "d" 2007   1.271293
"b" 2008  .8232493 "d" 2008 -1.6404753
"b" 2009  .3962567 "d" 2009  -.4025347
"c" 2007  .7346603 "d" 2007   1.271293
"c" 2008    .60687 "d" 2008 -1.6404753
"c" 2009  .6496394 "d" 2009  -.4025347
end

Thanks.

[Nick Cox]

Thanks for the detail. I see what you want, but can't at the moment see a quick way to get it. Someone else is likely to spot it.

[John DSouza]

Try the code below. It assumes each combination of v and year occurs once and only once. You will need to change it if you have any missing data.

It works by doing 6 reshapes and appending the results.

I have Stata version 14. As you have version 17, you might be able to do it using frames and it might be easier.

version 14.2
clear
input str8 v int year float x
"a" 2007   .5353563
"a" 2008   .9427623
"a" 2009   .2045144
"b" 2007  .34855065
"b" 2008   .8232493
"b" 2009   .3962567
"c" 2007   .7346603
"c" 2008     .60687
"c" 2009   .6496394
"d" 2007   1.271293
"d" 2008 -1.6404753
"d" 2009  -.4025347
end

local letters a b c d
local n : word count `letters'
local f = 0

// Loop through every possibility
forvalues i = 1/`= `n'-1' {
    forvalues j = `= `i'+1'/`n' {
        local f = `f' + 1
        local A : word `i' of `letters'
        local B : word `j' of `letters'    
        preserve
            keep if inlist(v, "`A'", "`B'")
            sort year v
            by year: gen j = _n
            qui: reshape wide v x, i(year) j (j)
            tempfile file`f'
            save `file`f''
        restore        
    }
}

// Append files
clear
forvalues i = 1/`f' {
    append using `file`i''
}
sort v1 v2 year
list

[River Huang]

Dear John, Thanks a lot, I will give it a try soon.

[Andrew Musau]

This looks like a joinby.

* Example generated by -dataex-. For more info, type help dataex
clear
input str8 v int year float x
"a" 2007   .5353563
"a" 2008   .9427623
"a" 2009   .2045144
"b" 2007  .34855065
"b" 2008   .8232493
"b" 2009   .3962567
"c" 2007   .7346603
"c" 2008     .60687
"c" 2009   .6496394
"d" 2007   1.271293
"d" 2008 -1.6404753
"d" 2009  -.4025347
end

preserve
rename * *2
gen year=year2
tempfile 2
save `2'
restore
joinby year using `2'
bys v (v2 year): drop if v>=v2

Res.:

. l, sepby(v)

     +----------------------------------------------+
     | v   year          x   v2   year2          x2 |
     |----------------------------------------------|
  1. | a   2007   .5353563    b    2007    .3485506 |
  2. | a   2008   .9427623    b    2008    .8232493 |
  3. | a   2009   .2045144    b    2009    .3962567 |
  4. | a   2007   .5353563    c    2007    .7346603 |
  5. | a   2008   .9427623    c    2008      .60687 |
  6. | a   2009   .2045144    c    2009    .6496394 |
  7. | a   2007   .5353563    d    2007    1.271293 |
  8. | a   2008   .9427623    d    2008   -1.640475 |
  9. | a   2009   .2045144    d    2009   -.4025347 |
     |----------------------------------------------|
 10. | b   2007   .3485506    c    2007    .7346603 |
 11. | b   2008   .8232493    c    2008      .60687 |
 12. | b   2009   .3962567    c    2009    .6496394 |
 13. | b   2007   .3485506    d    2007    1.271293 |
 14. | b   2008   .8232493    d    2008   -1.640475 |
 15. | b   2009   .3962567    d    2009   -.4025347 |
     |----------------------------------------------|
 16. | c   2007   .7346603    d    2007    1.271293 |
 17. | c   2008     .60687    d    2008   -1.640475 |
 18. | c   2009   .6496394    d    2009   -.4025347 |
     +----------------------------------------------+

[River Huang]

Dear Andrew, Thanks a lot for this interesting suggestion.