reshaping data over two dimensions

[Mina Wu]

Hi everyone.

I would like to reshape my data from wide to long, but over two dimension, one of which is a standard one-time dimension.
i have the data that have the format:

Dataset1

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float(x_hr047_2006 x_hr048_2006 x_hr047_2007 x_hr048_2007)
1  .16038707 .0000384255  .03993386 .000923258
2   .3477554 .0000822979  .05899953 .001369665
3 .001378539  .004831353 .009549395 .005290506
4  .21207225 .0000128496  .04594259  .01215579
5 .003936227  .000480553 .010989835 .005820832
end
------------------ copy up to and including the previous line ------------------

The last four digit of these "x" variables correspond to year, while "hr+3 digit number" within these "x" variables corresponds to another dimension. I want my dataset to looks like this

Dataset2

----------------------- copy starting from the next line -----------------------


	
	
* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id int year str5 y float x
1 2006 "HR047"   .16038707
1 2007 "HR047"   .03993386
2 2006 "HR047"    .3477554
2 2007 "HR047"   .05899953
1 2006 "HR048" .0000384255
1 2007 "HR048"  .000923258
2 2006 "HR048" .0000822979
2 2007 "HR048"  .001369665
...
end
------------------ copy up to and including the previous line ------------------

Listed 8 out of 8 observations

To conclude, I want to have X variable varying over id (as it already does) and two new variables - "year" and "Y".
The command

reshape long

seem to work only with year dimension (converts data from wide to long, using "year" identifies only). I always get an error when I try applying it.
Does anyone know how I can get Dataset2 from Dataset1 using "reshape" or any other state command?


Thanks in advance!

[Clyde Schechter]

Try this:

reshape long x_, i(id) j(hr_year) string
rename x_ x
split hr_year, gen(v) parse("_")
rename v1 y
rename v2 year
destring year, replace
drop hr_year

It can also be done by a series of 2 -reshape long- commands, one on year and the other on hr. But getting the list of variables right for that approach is difficult. I think doing it as a single reshape and then parsing hr and year out of the combination is easier.

[Mina Wu]

This worked perfectly fine! Thank you !