Converting data rows to columns

[Gunter Muller]

I copied my data from an excel file. The variables (C,D,E, F etc.) are years. For some reason, the year variables are shown as C,D,E etc, but they represent 1990, 1991, 1992. I want to transform my data into a format in which for example Austria is listed in a separate row for each observation of austria. Like:

Aut Austria C 76.05
Aut Austria D 76.62
Aut Austria E 80.51

However, after reviewing some other topics, I am still unable to do so. Any help is much appreciated.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str3 CountryCode str15 countryname str5(C D E F G H I J K)
"AUT" "Austria"         "76.05" "76.62" "80.51" "80.68" "81.73" "82.96"    "84.79"    "86.19"    "87.53"
"BEL" "Belgium"         "81.11" "80.87" "83.37" "85.59" "87.05" "88.08"    "88.64"    "89.64"    "90.82"
"CHE" "Switzerland"     "80.51" "80.52" "83.67" "84.17" "85.64" "86.23"    "86.37"    "87.23"    "88.74"
"CZE" "Czech Republic"  "."     "."     "."     "."     "64.56" "66.94"    "69.84"    "71.0"    "73.71"
"DEU" "Germany"         "63.69" "63.48" "72.02" "72.97" "74.6"  "75.57"    "76.88"    "78.12"    "80.31"
"DNK" "Denmark"         "75.9"  "76.62" "80.01" "81.21" "82.68" "83.97"    "84.69"    "84.9"    "86.14"
"ESP" "Spain"           "66.42" "67.71" "70.52" "72.51" "74.93" "76.26"    "77.3"    "78.06"    "79.44"
"EST" "Estonia"         "."     "."     "41.46" "43.1"  "46.11" "49.59"    "61.52"    "63.71"    "68.64"
"FIN" "Finland"         "63.72" "64.59" "69.17" "71.02" "73.32" "74.74"    "76.15"    "82.18"    "83.85"
"FRA" "France"          "74.06" "74.92" "78.25" "79.7"  "80.86" "80.53"    "81.72"    "81.96"    "83.45"
"GBR" "United Kingdom"  "80.46" "80.28" "82.28" "82.33" "84.02" "84.62"    "84.87"    "85.7"    "86.64"
"GRC" "Greece"          "48.64" "49.79" "62.04" "63.01" "66.72" "67.58"    "68.18"    "69.63"    "70.87"
"HUN" "Hungary"         "53.51" "58.99" "62.18" "65.91" "69.11" "72.34"    "74.98"    "76.99"    "79.13"
"IRL" "Ireland"         "72.29" "72.65" "74.76" "76.34" "78.02" "79.61"    "80.84"    "82.15"    "83.2" 
"ISL" "Iceland"         "53.08" "53.24" "55.02" "56.56" "63.72" "65.62"    "66.9"    "67.69"    "73.57"
"ITA" "Italy"           "65.35" "66.18" "68.93" "70.74" "73.09" "73.51"    "74.21"    "75.4"    "77.13"
"LUX" "Luxembourg"      "71.83" "72.0"  "74.54" "81.66" "82.4"  "76.83"    "77.16"    "77.96"    "78.56"
"LVA" "Latvia"          "."     "37.64" "38.6"  "39.3"  "40.66" "46.49"    "48.88"    "51.78"    "55.02"
"NLD" "Netherlands"     "81.57" "81.72" "83.74" "86.07" "86.49" "86.83"    "87.18"    "87.3"    "88.11"
"NOR" "Norway"          "75.17" "75.53" "77.61" "77.68" "78.52" "80.02"    "80.43"    "81.98"    "82.63"
"POL" "Poland"          "47.34" "47.53" "53.13" "59.0"  "62.34" "63.0"    "65.26"    "66.28"    "68.47"
"PRT" "Portugal"        "53.19" "53.5"  "61.25" "67.38" "71.0"  "72.42"    "73.74"    "75.6"    "77.13"
"SRB" "Serbia"          "39.43" "40.38" "45.38" "45.75" "45.69" "41.55"    "41.72"    "42.28"    "42.51"
"SVK" "Slovak Republic" "."     "."     "."     "."     "54.74" "56.49"    "61.55"    "63.89"    "66.13"
"SVN" "Slovenia"        "."     "."     "40.02" "43.5"  "47.59" "51.08"    "52.85"    "58.04"    "64.16"
"SWE" "Sweden"          "78.21" "78.86" "81.31" "81.06" "83.01" "84.32"    "85.0"    "85.66"    "87.45"
"TUR" "Turkey"          "47.1"  "44.73" "48.29" "51.51" "53.88" "59.11"    "59.93"    "60.64"    "62.54"
"USA" "United States"   "73.19" "72.6"  "74.4"  "74.57" "76.24" "76.6"    "78.13"    "78.59"    "79.19"
"CAN" "Canada"          "78.86" "78.83" "80.45" "81.52" "82.75" "83.85"    "84.98"    "85.42"    "86.69"
"AUS" "Australia"       "73.35" "73.99" "74.51" "76.17" "77.15" "78.64"    "78.98"    "78.36"    "79.13"
"JPN" "Japan"           "48.95" "49.57" "51.54" "56.84" "57.64" "57.69"    "53.75"    "58.21"    "59.32"
"KOR" "Korea"           "41.65" "41.09" "41.89" "43.7"  "50.11" "51.15"    "52.12"    "54.64"    "56.06"
end

[Clyde Schechter]

Thank you for using -dataex- on your very first post!

Do you really want to show C D E, etc. in the final result? Wouldn't you rather have a variable that contains the corresponding year number? I'm going to assume you would:

//    RENAME VARIABLES C THROUGH K TO REFLECT YEARS
forvalues i = 1/9 {
    local current_name: word `=`i'+2' of `c(ALPHA)'
    local new_name value`=1989 + `i''
    rename `current_name' `new_name'
}

//    NOW RESHAPE LONG
reshape long value, i(CountryCode) j(year)

The -reshape- command is one of those "bread and butter" commands that every regular Stata user needs to know, and uses on a frequent basis. It takes a bit of getting used to the i() j() framework, but after several tries, it suddenly becomes obvious and you will be able to use it instinctively. Do read the -help reshape- file and the linked section in the manual to learn it in detail.

[Gunter Muller]

Thanks a lot. It indeed corresponds to a year number, so your solution is entirely correct. I understand this is quite important for analysis in stata. Thanks again Clyde.