Variable names management for reshape

[Luca Giangregorio]

Hi all,

I have a wide dataset where most of the variables are at household level and the different columns represent the individuals. So, I need to reshape from wide to long.
All variables have the same pattern like: p6_1_1, where the last number is the one needed for the reshape.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(p6_3_1 p6_3_2 p6_3_3 p6_3_4 p6_3_5 p6_3_6 p6_3_7 p6_3_8 p6_3_9 p6_4_1 p6_4_2 p6_4_3 p6_4_4 p6_4_5 p6_4_6 p6_4_7 p6_4_8 p6_4_9)
5 . . . . . . . . 11  . . . . . . . .
3 1 . . . . . . .  8  3 . . . . . . .
5 5 . . . . . . .  6  6 . . . . . . .
5 4 5 . . . . . .  7  3 6 . . . . . .
4 2 4 . . . . . .  3 14 9 . . . . . .
end

However, I'm struggling on how managing the variables name to reshape the dataset as using

 reshape long p*_@, i(h_number) j(ind_number)

of course doesn't work.
Is there a way to have something like

 reshape long `item'_@, i(h_number) j(ind_number)

where the `item' corresponds to the first part of the variable name p6_1_?
Thanks for your support!

[Øyvind Snilsberg]

perhaps,

g h_number = _n
reshape long p6_3_ p6_4_, i(h_number) j(ind_number)

?

[Luca Giangregorio]

Yes, this is works perfectly. But the problem is that I have hundreds of variables and I would like to have a kind of local taking exactly only the first part of the variable name (p6_3_).
Sorry if I wasn't that clear

[Øyvind Snilsberg]

Not quite what you are looking for but perhaps it is possible to loop over groups of variables,

clear
input byte(p6_3_1 p6_3_2 p6_3_3 p6_3_4 p6_3_5 p6_3_6 p6_3_7 p6_3_8 p6_3_9 p6_4_1 p6_4_2 p6_4_3 p6_4_4 p6_4_5 p6_4_6 p6_4_7 p6_4_8 p6_4_9)
5 . . . . . . . . 11  . . . . . . . .
3 1 . . . . . . .  8  3 . . . . . . .
5 5 . . . . . . .  6  6 . . . . . . .
5 4 5 . . . . . .  7  3 6 . . . . . .
4 2 4 . . . . . .  3 14 9 . . . . . .
end
g h_number = _n
tempfile file
save `file'

forv i=3/4 {
use h_number p6_`i'_* using `file'
reshape long p6_`i'_ , i(h_number) j(ind_number)
tempfile file`i'
save `file`i''
}

forv i=3/4 {
merge 1:1 h_number ind_number using `file`i'', nogen
}

[Nick Cox]

This may get you closer. The idea is as you have it, namely that the stub stops at the last underscore. Note that although your example suffixes go up to 9, suffixes 10 up should work too.


https://www.stata.com/support/faqs/d...-with-reshape/ has another example in similar spirit.

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(p6_3_1 p6_3_2 p6_3_3 p6_3_4 p6_3_5 p6_3_6 p6_3_7 p6_3_8 p6_3_9 p6_4_1 p6_4_2 p6_4_3 p6_4_4 p6_4_5 p6_4_6 p6_4_7 p6_4_8 p6_4_9)
5 . . . . . . . . 11  . . . . . . . .
3 1 . . . . . . .  8  3 . . . . . . .
5 5 . . . . . . .  6  6 . . . . . . .
5 4 5 . . . . . .  7  3 6 . . . . . .
4 2 4 . . . . . .  3 14 9 . . . . . .
end

foreach v of var p* {
    local stub = substr("`v'", 1, strrpos("`v'", "_"))
    local stubs `stubs' `stub'
}

local stubs : list uniq stubs

gen long id = _n

reshape long `stubs', i(id) j(which)

list

     +----------------------------+
     | id   which   p6_3_   p6_4_ |
     |----------------------------|
  1. |  1       1       5      11 |
  2. |  1       2       .       . |
  3. |  1       3       .       . |
  4. |  1       4       .       . |
  5. |  1       5       .       . |
     |----------------------------|
  6. |  1       6       .       . |
  7. |  1       7       .       . |
  8. |  1       8       .       . |
  9. |  1       9       .       . |
 10. |  2       1       3       8 |
     |----------------------------|
 11. |  2       2       1       3 |
 12. |  2       3       .       . |
 13. |  2       4       .       . |
 14. |  2       5       .       . |
 15. |  2       6       .       . |
     |----------------------------|
 16. |  2       7       .       . |
 17. |  2       8       .       . |
 18. |  2       9       .       . |
 19. |  3       1       5       6 |
 20. |  3       2       5       6 |
     |----------------------------|
 21. |  3       3       .       . |
 22. |  3       4       .       . |
 23. |  3       5       .       . |
 24. |  3       6       .       . |
 25. |  3       7       .       . |
     |----------------------------|
 26. |  3       8       .       . |
 27. |  3       9       .       . |
 28. |  4       1       5       7 |
 29. |  4       2       4       3 |
 30. |  4       3       5       6 |
     |----------------------------|
 31. |  4       4       .       . |
 32. |  4       5       .       . |
 33. |  4       6       .       . |
 34. |  4       7       .       . |
 35. |  4       8       .       . |
     |----------------------------|
 36. |  4       9       .       . |
 37. |  5       1       4       3 |
 38. |  5       2       2      14 |
 39. |  5       3       4       9 |
 40. |  5       4       .       . |
     |----------------------------|
 41. |  5       5       .       . |
 42. |  5       6       .       . |
 43. |  5       7       .       . |
 44. |  5       8       .       . |
 45. |  5       9       .       . |
     +----------------------------+

[Luca Giangregorio]

Thanks Nick, a perfect solution!