Reshaping data from wide to long, creating new j variable

[Steph Awote]

Hi,

UGENT HELP PLEASE

I need to reshape my data from wide too long in order to merge it with already existing merged panel data. I have created a new variable, country, with a list of 38 countries. These countries have data on natural resources rents from years 1990 t0 2017. I need to make all these values into 1 column. Therefore creating a new variable, year. I have performed the command below but no luck;

The letters B, C, D...are the variables representing the years 1990, 1991, 1992 ....which I am trying to merge into one column year.
I will also need to create a variable, nat, that will consists of the values of the total rents in all the years for each country in one column.


Thank you

[William Lisowski]

The error mesasage answers your question. A required part of the reshape command is either the word "wide" or "long". Your command should be something like

reshape long A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA BB AC, j(year) i(country)

In the future, you would be well advised to omit the claim of urgency - almost every question is urgent to the person who asks it.

[Steph Awote]

Thank you William.

I added the the word "long" and it didn't seem to work as it said "no xii variables found" however stata has read the letters as my variables so that is what I don't understand what to do next

[William Lisowski]

Actually, I didn't look very closely at your question since the error message addressed the immediate problem.

Perhaps the following example code will do what you want.

rename (B-AC) (rent#), addunumber(1990)
reshape long rent, i(country) j(year)

Note that for this code to work, it depends on the variables B-AC to be in that order in your data, with no other variables between them. I'm guessing you imported the data from Excel so that probably won't be a problem.

 list

     +------------------------+
     | country    b    c    d |
     |------------------------|
  1. |       1   11   12   13 |
  2. |       2   21   22   23 |
     +------------------------+

. rename (b-d) (rent#), addnumber(1990)

. reshape long rent, i(country) j(year)
(note: j = 1990 1991 1992)

Data                               wide   ->   long
-----------------------------------------------------------------------------
Number of obs.                        2   ->       6
Number of variables                   4   ->       3
j variable (3 values)                     ->   year
xij variables:
             rent1990 rent1991 rent1992   ->   rent
-----------------------------------------------------------------------------

. list, sepby(country)

     +-----------------------+
     | country   year   rent |
     |-----------------------|
  1. |       1   1990     11 |
  2. |       1   1991     12 |
  3. |       1   1992     13 |
     |-----------------------|
  4. |       2   1990     21 |
  5. |       2   1991     22 |
  6. |       2   1992     23 |
     +-----------------------+

[Steph Awote]

I tried the first command and it worked well. However when trying to reshape, i got this;

reshape long rent, i(country) j(year)
(note: j = 1990 1991 1992 1993 1994 1995 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2
> 007 2008 2009 2010 2011 2012 2013 2014 2015 2016 2017)

variable rent2013 type mismatch with other rent variables

Is there a way to fix this error?

[Moniek Bresser]

Dear Steph,

This could mean that the values of the rent2013 variable are of a different format than the other year variables.

Perhaps double check this first?

Best wishes,

Moniek

[Steph Awote]

I see that the values for the years 1990-2012 are in red text so perhaps I have to de-string them?

The valued from 2013 to 2017 are in black text.

I dropped the rent2013 variable to see if the problem would go, however rent2014 had the same problem of being mismatched with other variables, I'm assuming this would be the case with the future year variables as they are in black text

[Nick Cox]

I am guessing that you imported these data from a spreadsheet.

Some variables that "should be" numeric could have been read in as string for several reasons, e.g.

* metadata in the first row or rows of the spreadsheet;

* characters that Stata can't understand as numeric here and there (e.g. "NA" for missings)

* textual comment sprinkled in the rest of a worksheet (e.g. extra notes or footnotes).

Sometimes it's easiest to revisit the data import checking more carefully that you import numeric values, and only numeric values. db import excel helps here.

As you are aware, destring also exists to serve.

[William Lisowski]

My guess is that when you imported your data from Excel, there were cells in columns Y and beyond have non-numeric data, so Stata imports the variable as a string rather than a number.

Yes, all your variables B through AC must be the same type - either all numeric or all string - before starting on this.

tab Y if real(Y)==.

and

list country Y if real(Y)==.

will show you what the offending observations are, but it will up to you to decide what to do about them.

[Steph Awote]

I have successfully merged the data. I found that the variable types were different. Thank you William and thank you Moniek

[Steph Awote]

Thank you Nick Cox