Reshaping multiple variables at once

[Chinmay Sharma]

Hi All,

I have data that resembles the following:

* Example generated by -dataex-. To install: ssc install dataex
clear
input float id str2 varlist float(yr1995 yr1996 yr1997)
1 "x1" 2 23 1
1 "x2" 3  4 3
1 "x3" 2  2 2
2 "x1" 3  2 2
2 "x2" 2  2 2
2 "x2" 1  2 3
end

In the above, I have information by individual id, for different years, for the variables x1, x2 and x3. I wish to reshape this data to long format, where each variable (x1, x2 and x3) is stacked in an independent column, where each entry in the column is the value of that variable for a particular year.

Normally, the reshape command would do the trick, but the fact that there are multiple variables complicates this. Is there a straightforward way to implement this?


Many thanks,
CS

[Wouter Wakker]

Assuming that your last entry if x2 should be x3:

reshape long yr, i(id varlist) j(year)
reshape wide yr, i(id year) j(varlist) string
rename yr* *

[Chinmay Sharma]

Assuming that your last entry if x2 should be x3:

reshape long yr, i(id varlist) j(year)
reshape wide yr, i(id year) j(varlist) string
rename yr* *

Thank you so much for your help. I should have written my query out better. The variables themselves, as you have correctly pointed out, are indeed string. They are not so straightforward as pointed out in my example. In particular, x1 is "GDP per capita", x2 is "Gini Coefficient" and x3 is "Income share of the lowest 40%". Using your code on these variable names does not work, because each word in the varlist is treated as a separate variable, and hence no data is identified.. Is there any other way to do this?

[Wouter Wakker]

Try converting that variable first to make sure that the string contents are suitable as variable names. For example GDP_per_capita instead of GDP per capita. You can easily do this using the strtoname function. I think that should solve it but please post back if you have any more problems.

[Chinmay Sharma]

Try converting that variable first to make sure that the string contents are suitable as variable names. For example GDP_per_capita instead of GDP per capita. You can easily do this using the strtoname function. I think that should solve it but please post back if you have any more problems.

Thanks a lot! I converted it to string contents that are suitable variable name. The reshape long works fine, but the reshape wide returns an error message "yrGDP_per_capita invalid variable name"

I am not sure why this is the case, as this does not seem to be an invalid name..

[Chinmay Sharma]

I fixed it- it turns out I cant create variable names with characters >32.
What I did was, to first use the -substr- command:

g idnew=substr(id,1,15)
drop id
rename idnew id
reshape wide yr, i(id year) j(varlist) string
rename yr* *

Many thanks for your help!