How to get rows into columns when non transposable?

[Christine Everaerts]

Hi all ,

I was wondering whether someone could help me with a command so I can convert my data into the desired format.
Current data (subset):

Countrycode	Seriesname	yr1996	yr1997	yr1998	yr1999	yr2000
BEL	GDP (constant 2015 US$)	a	b	c	d	e
BEL	GDP per capita (constant 2015 US$)	...	...	...	...	...
BEL	Population	...	...	...	...	...
FRA	GDP (constant 2015 US$)	...	...	...	...	...
FRA	GDP per capita (constant 2015 US$)	...	...	...	...	...

I want to convert this into a format where I have the variable "countrycode" "Population, total" "GDP (constant 2015 US$)" "GDP per capita (constant 2015 US$" and "Year".
With Year from 1996-2000 corresponding to the right value of population, gdp and gdp per capita.

So like this:

Countrycode	GDP (constant 2015 US$)	GDP per capita (constant 2015 US$	Population, total	Year
BEL	a			1996
BEL	b			1997
BEL	c			1998
BEL	d			1999
BEL	e			2000
FRA
FRA
FRA
FRA
FRA

I tried transposing it, but it didn't work because it's not a transposable matrix (doesn't have dimension = n x n)

Someone who can help me?

Thanks!

[Clyde Schechter]

Nobody can help you do the impossible. The values in Seriesname, which you then want to become variable names in the end result, are not legal Stata variable names because they contain spaces and non-alphanumeric characters. The closest you can come is this:

reshape long yr, i(countrycode seriesname) j(year)
replace seriesname = substr(strtoname(seriesname), 1, 30)
reshape wide yr, i(countrycode year) j(seriesname) string
rename yr* *

[Christine Everaerts]

Is there a possibility to rename all the cells containing "GDP (constant 2015 US$)" to "gdp"?

I have to use stata for my thesis, but I have never used it before..

[William Lisowski]

Here I have expanded on Clyde's answer, starting by presenting example data using the dataex command. More on this later in the post.

* Example generated by -dataex-. For more info, type help dataex
clear
input str3 countrycode str34 seriesname float(yr1996 yr1997 yr1998 yr1999 yr2000)
"BEL" "GDP (constant 2015 US$)"            11 12 13 14 15
"BEL" "GDP per capita (constant 2015 US$)" 21 22 23 24 25
"BEL" "Population"                         31 32 33 34 35
"FRA" "GDP (constant 2015 US$)"            41 42 43 44 45
"FRA" "GDP per capita (constant 2015 US$)" 51 52 53 45 55
"FRA" "Population"                         61 62 63 64 65
end

replace seriesname = "GDP" if seriesname=="GDP (constant 2015 US$)"
replace seriesname = "GDPpc" if seriesname=="GDP per capita (constant 2015 US$)"

reshape long yr, i(countrycode seriesname) j(year)
rename yr data
list, clean noobs abbreviate(16)
reshape wide data, i(countrycode year) j(seriesname) string
rename data* *
list, clean noobs abbreviate(16)

. reshape long yr, i(countrycode seriesname) j(year)
(j = 1996 1997 1998 1999 2000)

Data                               Wide   ->   Long
-----------------------------------------------------------------------------
Number of observations                6   ->   30          
Number of variables                   7   ->   4           
j variable (5 values)                     ->   year
xij variables:
               yr1996 yr1997 ... yr2000   ->   yr
-----------------------------------------------------------------------------

. rename yr data

. list, clean noobs abbreviate(16)

    countrycode   seriesname   year   data  
            BEL          GDP   1996     11  
            BEL          GDP   1997     12  
            BEL          GDP   1998     13  
            BEL          GDP   1999     14  
            BEL          GDP   2000     15  
            BEL        GDPpc   1996     21  
            BEL        GDPpc   1997     22  
            BEL        GDPpc   1998     23  
            BEL        GDPpc   1999     24  
            BEL        GDPpc   2000     25  
            BEL   Population   1996     31  
            BEL   Population   1997     32  
            BEL   Population   1998     33  
            BEL   Population   1999     34  
            BEL   Population   2000     35  
            FRA          GDP   1996     41  
            FRA          GDP   1997     42  
            FRA          GDP   1998     43  
            FRA          GDP   1999     44  
            FRA          GDP   2000     45  
            FRA        GDPpc   1996     51  
            FRA        GDPpc   1997     52  
            FRA        GDPpc   1998     53  
            FRA        GDPpc   1999     45  
            FRA        GDPpc   2000     55  
            FRA   Population   1996     61  
            FRA   Population   1997     62  
            FRA   Population   1998     63  
            FRA   Population   1999     64  
            FRA   Population   2000     65  

. reshape wide data, i(countrycode year) j(seriesname) string
(j = GDP GDPpc Population)

Data                               Long   ->   Wide
-----------------------------------------------------------------------------
Number of observations               30   ->   10          
Number of variables                   4   ->   5           
j variable (3 values)        seriesname   ->   (dropped)
xij variables:
                                   data   ->   dataGDP dataGDPpc dataPopulation
-----------------------------------------------------------------------------

. rename data* *

. list, clean noobs abbreviate(16)

    countrycode   year   GDP   GDPpc   Population  
            BEL   1996    11      21           31  
            BEL   1997    12      22           32  
            BEL   1998    13      23           33  
            BEL   1999    14      24           34  
            BEL   2000    15      25           35  
            FRA   1996    41      51           61  
            FRA   1997    42      52           62  
            FRA   1998    43      53           63  
            FRA   1999    44      45           64  
            FRA   2000    45      55           65  

.

Now, some advice on using Stata and on using Statalist.

Since you identify as a new user of Stata, let me say that I'm sympathetic to you as a new user of Stata - there is quite a lot to absorb.

When I began using Stata in a serious way, I started, as have others here, by reading my way through the Getting Started with Stata manual relevant to my setup. Chapter 18 then gives suggested further reading, much of which is in the Stata User's Guide, and I worked my way through much of that reading as well. All of these manuals are included as PDFs in the Stata installation and are accessible from within Stata - for example, through the PDF Documentation section of Stata's Help menu.

The objective in doing the reading was not so much to master Stata - I'm still far from that goal - as to be sure I'd become familiar with a wide variety of important basic techniques, so that when the time came that I needed them, I might recall their existence, if not the full syntax, and know how to find out more about them in the help files and PDF manuals.

Stata supplies exceptionally good documentation that amply repays the time spent studying it - there's just a lot of it. The path I followed surfaces the things you need to know to get started in a hurry and to work effectively.

Stata also supples YouTube videos, if that's your thing.

And some advice on using Statalist.

Please take a few moments to review the Statalist FAQ linked to from the top of the page, as well as from the Advice on Posting link on the page you used to create your post. Note especially sections 9-12 on how to best pose your question. It is particularly helpful to copy commands and output from your Stata Results window and paste them into your Statalist post using code delimiters [CODE] and [/CODE], and to use the dataex command to provide sample data, as described in section 12 of the FAQ.

Descriptions of data are well-meant but insufficient to help those who want to help you. Even the best descriptions of data are no substitute for an actual example of the data. There are many ways your data might be organized that are consistent with your description, and each would require a somewhat different approach. In order to get a helpful response, you need to show some example data.

Be sure to use the dataex command to do this. 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 and 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 the dataex command.

The more you help others understand your problem, the more likely others are to be able to help you solve your problem.