Transpose dataset with variable names becoming values

[Joan Marti]

Hi,

I have a sub-set of my larger dataset with the following format:

country1	country2	country3	country4	country5
0.05	0.32	0.01	-0.32	-0.45

I want it to look like this:

Country	value
country1	0.05
country2	0.32
country3	0.01
country4	-0.32
country5	-0.45

I tried using reshape but I wasn't able to keep the country names as values in a new variable. Is it possible to reshape the data like this?

Many thanks!

[Clyde Schechter]

Well, based on what you show, there are no country names in the data to start with. -reshape- isn't going to create them. I suspect the confusion is arising because the tableau you display in #1 is not actually what your data look like. Nor do you show how you tried to use -reshape-, so it is impossible to say what might have gone wrong or how to correct it. If you post back, using the -dataex- command to show an actual example from your real Stata data set, I'm sure this can be easily handled.

If you are running version 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.

When asking for help with code, always show example data. When showing example data, always use -dataex-.

[William Lisowski]

The following example perhaps will start you in a useful direction. This code will fail if any of your country names are longer than 27 characters, or if you happen to already have a variable name that begins "value". There are probably other circumstances under which it will fail, but careful study of this example to gain an understanding of how it works should allow you to resolve any problems. If not, the advice in post #2 pertains: show realistic example data using dataex.

* Example generated by -dataex-. For more info, type help dataex
clear
input float(country1 country2 country3 country4 country5)
.05 .32 .01 -.32 -.45
end

generate id = _n
rename (country1-country5) (value=)
reshape long value, i(id) j(country) string
list, clean

. list, clean

       id    country   value  
  1.    1   country1     .05  
  2.    1   country2     .32  
  3.    1   country3     .01  
  4.    1   country4    -.32  
  5.    1   country5    -.45

[Joan Marti]

This works perfectly, thanks a lot!