How to convert some rows into columns to create panel structure?

[Titir Bhattacharya]

Hi,

I'm using some data from spreadsheets where the data is in the following layout:

State	2005	2006	2007
A	12	13	14
B	15	16	17
C	20	19	18

I've imported this into stata and want to arrange the data in the following layout:

* Example generated by -dataex-. For more info, type help dataex
clear
input str5 var1 str4(var2 var3)
"State" "Year" "Rate"
"A"     "2005" "12"  
"A"     "2006" "13"  
"A"     "2007" "14"  
"B"     "2005" "15"  
"B"     "2006" "16"  
"B"     "2007" "17"  
"C"     "2005" "20"  
"C"     "2006" "19"  
"C"     "2007" "18"  
end

The original data doesn't have column indicating variable name-rate ( it's only available in the file description).

Would appreciate any help.

Thanks!

[Andrew Musau]

This is a straightforward reshape long. Make sure you prefix your years so that they are legal Stata names.

* Example generated by -dataex-. For more info, type help dataex
clear
input str1 state byte(varname2005 varname2006 varname2007)
"A" 12 13 14
"B" 15 16 17
"C" 20 19 18
end

reshape long varname, i(state) j(year)

Res.:

. l, sepby(state)

     +------------------------+
     | state   year   varname |
     |------------------------|
  1. |     A   2005        12 |
  2. |     A   2006        13 |
  3. |     A   2007        14 |
     |------------------------|
  4. |     B   2005        15 |
  5. |     B   2006        16 |
  6. |     B   2007        17 |
     |------------------------|
  7. |     C   2005        20 |
  8. |     C   2006        19 |
  9. |     C   2007        18 |
     +------------------------+