Cleaning imported excel file - stuck on creating year variable

[Cassie Wright]

Hello! I have imported an excel file into Stata and I am attempting create panel data. I am trying to create a long panel format, where country, year, PRN, CL, and Status are side by side. I am struggling with how to manage the years, as it skips every two variables.

clear
input str30 A str7 BS str3 BT str6 BU str7 BV str3 BW str6 BX str7 BY
"Year(s) Under Review"     "1996" ""    ""       "1997" ""    ""       "1998"
""                         "PR"   "CL " "Status" "PR"   "CL " "Status" "PR"  
"Afghanistan"              "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Albania"                  "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
"Algeria"                  "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Andorra"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Angola"                   "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Antigua and Barbuda"      "4"    "3"   "PF"     "4"    "3"   "PF"     "4"  
"Argentina"                "2"    "3"   "F"      "2"    "3"   "F"      "3"  
"Armenia"                  "5"    "4"   "PF"     "5"    "4"   "PF"     "4"  
"Australia"                "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Austria"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Azerbaijan"               "6"    "5"   "NF"     "6"    "4"   "PF"     "6"  
"Bahamas"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Bahrain"                  "7"    "6"   "NF"     "7"    "6"   "NF"     "7"  
"Bangladesh"               "2"    "4"   "PF"     "2"    "4"   "PF"     "2"  
"Barbados"                 "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Belarus"                  "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Belgium"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Belize"                   "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Benin"                    "2"    "2"   "F"      "2"    "2"   "F"      "2"  
"Bhutan"                   "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Bolivia"                  "2"    "3"   "F"      "1"    "3"   "F"      "1"  
"Bosnia and Herzegovina"   "5"    "5"   "PF"     "5"    "5"   "PF"     "5"  
"Botswana"                 "2"    "2"   "F"      "2"    "2"   "F"      "2"  
"Brazil"                   "2"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Brunei"                   "7"    "5"   "NF"     "7"    "5"   "NF"     "7"  
"Bulgaria"                 "2"    "3"   "F"      "2"    "3"   "F"      "2"  
"Burkina Faso"             "5"    "4"   "PF"     "5"    "4"   "PF"     "5"  
"Burundi"                  "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Cabo Verde"               "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Cambodia"                 "6"    "6"   "NF"     "7"    "6"   "NF"     "6"  
"Cameroon"                 "7"    "5"   "NF"     "7"    "5"   "NF"     "7"  
"Canada"                   "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Central African Republic" "3"    "5"   "PF"     "3"    "5"   "PF"     "3"  
"Chad"                     "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Chile"                    "2"    "2"   "F"      "2"    "2"   "F"      "3"  
"China"                    "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Colombia"                 "4"    "4"   "PF"     "4"    "4"   "PF"     "3"  
"Comoros"                  "4"    "4"   "PF"     "5"    "4"   "PF"     "5"  
"Congo (Brazzaville)"      "4"    "4"   "PF"     "7"    "5"   "NF"     "7"  
"Congo (Kinshasa)"         "7"    "6"   "NF"     "7"    "6"   "NF"     "7"  
"Costa Rica"               "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Cote d'Ivoire"            "6"    "5"   "NF"     "6"    "4"   "NF"     "6"  
"Croatia"                  "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
"Cuba"                     "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Cyprus"                   "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Czech Republic"           "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Czechoslovakia"           "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Denmark"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Djibouti"                 "5"    "6"   "NF"     "5"    "6"   "NF"     "5"  
"Dominica"                 "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Dominican Republic"       "3"    "3"   "PF"     "3"    "3"   "PF"     "2"  
"Ecuador"                  "2"    "4"   "PF"     "3"    "3"   "PF"     "2"  
"Egypt"                    "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"El Salvador"              "3"    "3"   "PF"     "2"    "3"   "F"      "2"  
"Equatorial Guinea"        "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Eritrea"                  "6"    "4"   "PF"     "6"    "4"   "PF"     "6"  
"Estonia"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Eswatini"                 "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Ethiopia"                 "4"    "5"   "PF"     "4"    "5"   "PF"     "4"  
"Fiji"                     "4"    "3"   "PF"     "4"    "3"   "PF"     "4"  
"Finland"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"France"                   "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Gabon"                    "5"    "4"   "PF"     "5"    "4"   "PF"     "5"  
"Georgia"                  "4"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Germany"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Germany, E. "             "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Germany, W. "             "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Ghana"                    "3"    "4"   "PF"     "3"    "3"   "PF"     "3"  
"Greece"                   "1"    "3"   "F"      "1"    "3"   "F"      "1"  
"Grenada"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Guatemala"                "3"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Guinea"                   "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Guinea-Bissau"            "3"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Guyana"                   "2"    "2"   "F"      "2"    "2"   "F"      "2"  
"Haiti"                    "4"    "5"   "PF"     "4"    "5"   "PF"     "5"  
"Honduras"                 "3"    "3"   "PF"     "2"    "3"   "F"      "2"  
"Hungary"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Iceland"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"India"                    "2"    "4"   "PF"     "2"    "4"   "PF"     "2"  
"Indonesia"                "7"    "5"   "NF"     "7"    "5"   "NF"     "6"  
"Iran"                     "6"    "7"   "NF"     "6"    "7"   "NF"     "6"  
"Iraq"                     "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Ireland"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Israel"                   "1"    "3"   "F"      "1"    "3"   "F"      "1"  
"Italy"                    "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Jamaica"                  "2"    "3"   "F"      "2"    "3"   "F"      "2"  
"Japan"                    "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Jordan"                   "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
"Kazakhstan"               "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Kenya"                    "7"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Kiribati"                 "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Kosovo"                   "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Kuwait"                   "5"    "5"   "PF"     "5"    "5"   "PF"     "5"  
"Kyrgyzstan"               "4"    "4"   "PF"     "4"    "4"   "PF"     "5"  
"Laos"                     "7"    "6"   "NF"     "7"    "6"   "NF"     "7"  
"Latvia"                   "2"    "2"   "F"      "1"    "2"   "F"      "1"  
"Lebanon"                  "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Lesotho"                  "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
end

Thank you in advance!

[Clyde Schechter]

foreach x in BU BT BS {
    replace `x' = trim(itrim(`x'))
    rename `x' `=`x'[2]+BS[1]'
}
foreach x in BX BW BV {
    replace `x' = trim(itrim(`x'))
    rename `x' `=`x'[2]+BV[1]'
}
rename BY PR1998
rename A country
drop in 1/2

reshape long PR CL Status, i(country) j(year)
foreach v of varlist PR CL {
    replace `v' = "" if `v' == "-"
    destring `v', replace
}

[Andrew Musau]

The Freedom House index data is evenly ordered, so I would consider the first and last Excel cells (highlighted)

* Example generated by -dataex-. To install: ssc install dataex
clear
input str30 A str7 BS str3 BT str6 BU str7 BV str3 BW str6 BX str7 BY
"Year(s) Under Review"     "1996" ""    ""       "1997" ""    ""       "1998"
""                         "PR"   "CL " "Status" "PR"   "CL " "Status" "PR"  
"Afghanistan"              "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Albania"                  "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
"Algeria"                  "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Andorra"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Angola"                   "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Antigua and Barbuda"      "4"    "3"   "PF"     "4"    "3"   "PF"     "4"  
"Argentina"                "2"    "3"   "F"      "2"    "3"   "F"      "3"  
"Armenia"                  "5"    "4"   "PF"     "5"    "4"   "PF"     "4"  
"Australia"                "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Austria"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Azerbaijan"               "6"    "5"   "NF"     "6"    "4"   "PF"     "6"  
"Bahamas"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Bahrain"                  "7"    "6"   "NF"     "7"    "6"   "NF"     "7"  
"Bangladesh"               "2"    "4"   "PF"     "2"    "4"   "PF"     "2"  
"Barbados"                 "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Belarus"                  "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Belgium"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Belize"                   "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Benin"                    "2"    "2"   "F"      "2"    "2"   "F"      "2"  
"Bhutan"                   "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Bolivia"                  "2"    "3"   "F"      "1"    "3"   "F"      "1"  
"Bosnia and Herzegovina"   "5"    "5"   "PF"     "5"    "5"   "PF"     "5"  
"Botswana"                 "2"    "2"   "F"      "2"    "2"   "F"      "2"  
"Brazil"                   "2"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Brunei"                   "7"    "5"   "NF"     "7"    "5"   "NF"     "7"  
"Bulgaria"                 "2"    "3"   "F"      "2"    "3"   "F"      "2"  
"Burkina Faso"             "5"    "4"   "PF"     "5"    "4"   "PF"     "5"  
"Burundi"                  "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Cabo Verde"               "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Cambodia"                 "6"    "6"   "NF"     "7"    "6"   "NF"     "6"  
"Cameroon"                 "7"    "5"   "NF"     "7"    "5"   "NF"     "7"  
"Canada"                   "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Central African Republic" "3"    "5"   "PF"     "3"    "5"   "PF"     "3"  
"Chad"                     "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Chile"                    "2"    "2"   "F"      "2"    "2"   "F"      "3"  
"China"                    "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Colombia"                 "4"    "4"   "PF"     "4"    "4"   "PF"     "3"  
"Comoros"                  "4"    "4"   "PF"     "5"    "4"   "PF"     "5"  
"Congo (Brazzaville)"      "4"    "4"   "PF"     "7"    "5"   "NF"     "7"  
"Congo (Kinshasa)"         "7"    "6"   "NF"     "7"    "6"   "NF"     "7"  
"Costa Rica"               "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Cote d'Ivoire"            "6"    "5"   "NF"     "6"    "4"   "NF"     "6"  
"Croatia"                  "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
"Cuba"                     "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Cyprus"                   "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Czech Republic"           "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Czechoslovakia"           "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Denmark"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Djibouti"                 "5"    "6"   "NF"     "5"    "6"   "NF"     "5"  
"Dominica"                 "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Dominican Republic"       "3"    "3"   "PF"     "3"    "3"   "PF"     "2"  
"Ecuador"                  "2"    "4"   "PF"     "3"    "3"   "PF"     "2"  
"Egypt"                    "6"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"El Salvador"              "3"    "3"   "PF"     "2"    "3"   "F"      "2"  
"Equatorial Guinea"        "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Eritrea"                  "6"    "4"   "PF"     "6"    "4"   "PF"     "6"  
"Estonia"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Eswatini"                 "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Ethiopia"                 "4"    "5"   "PF"     "4"    "5"   "PF"     "4"  
"Fiji"                     "4"    "3"   "PF"     "4"    "3"   "PF"     "4"  
"Finland"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"France"                   "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Gabon"                    "5"    "4"   "PF"     "5"    "4"   "PF"     "5"  
"Georgia"                  "4"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Germany"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Germany, E. "             "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Germany, W. "             "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Ghana"                    "3"    "4"   "PF"     "3"    "3"   "PF"     "3"  
"Greece"                   "1"    "3"   "F"      "1"    "3"   "F"      "1"  
"Grenada"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Guatemala"                "3"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Guinea"                   "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Guinea-Bissau"            "3"    "4"   "PF"     "3"    "4"   "PF"     "3"  
"Guyana"                   "2"    "2"   "F"      "2"    "2"   "F"      "2"  
"Haiti"                    "4"    "5"   "PF"     "4"    "5"   "PF"     "5"  
"Honduras"                 "3"    "3"   "PF"     "2"    "3"   "F"      "2"  
"Hungary"                  "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Iceland"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"India"                    "2"    "4"   "PF"     "2"    "4"   "PF"     "2"  
"Indonesia"                "7"    "5"   "NF"     "7"    "5"   "NF"     "6"  
"Iran"                     "6"    "7"   "NF"     "6"    "7"   "NF"     "6"  
"Iraq"                     "7"    "7"   "NF"     "7"    "7"   "NF"     "7"  
"Ireland"                  "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Israel"                   "1"    "3"   "F"      "1"    "3"   "F"      "1"  
"Italy"                    "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Jamaica"                  "2"    "3"   "F"      "2"    "3"   "F"      "2"  
"Japan"                    "1"    "2"   "F"      "1"    "2"   "F"      "1"  
"Jordan"                   "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
"Kazakhstan"               "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Kenya"                    "7"    "6"   "NF"     "6"    "6"   "NF"     "6"  
"Kiribati"                 "1"    "1"   "F"      "1"    "1"   "F"      "1"  
"Kosovo"                   "-"    "-"   "-"      "-"    "-"   "-"      "-"  
"Kuwait"                   "5"    "5"   "PF"     "5"    "5"   "PF"     "5"  
"Kyrgyzstan"               "4"    "4"   "PF"     "4"    "4"   "PF"     "5"  
"Laos"                     "7"    "6"   "NF"     "7"    "6"   "NF"     "7"  
"Latvia"                   "2"    "2"   "F"      "1"    "2"   "F"      "1"  
"Lebanon"                  "6"    "5"   "NF"     "6"    "5"   "NF"     "6"  
"Lesotho"                  "4"    "4"   "PF"     "4"    "4"   "PF"     "4"  
end

local year 1
foreach var of varlist BS-BY{
    local name = "`=trim(`var'[2])'" + string(`=1995 +ceil(`year'/3)')
    rename `var' `name'
    local ++year
}
rename A country
reshape long PR CL Status, i(country) j(year)
keep country year PR CL Status

Res.:

. l, sep(0)

     +-------------------------------------------------------+
     |                  country   year     PR    CL   Status |
     |-------------------------------------------------------|
  1. |                            1996     PR   CL    Status |
  2. |                            1997     PR   CL    Status |
  3. |                            1998     PR                |
  4. |              Afghanistan   1996      7     7       NF |
  5. |              Afghanistan   1997      7     7       NF |
  6. |              Afghanistan   1998      7                |
  7. |                  Albania   1996      4     4       PF |
  8. |                  Albania   1997      4     4       PF |
  9. |                  Albania   1998      4                |
 10. |                  Algeria   1996      6     6       NF |
 11. |                  Algeria   1997      6     6       NF |
 12. |                  Algeria   1998      6                |
 13. |                  Andorra   1996      1     1        F |
 14. |                  Andorra   1997      1     1        F |
 15. |                  Andorra   1998      1                |
 16. |                   Angola   1996      6     6       NF |
 17. |                   Angola   1997      6     6       NF |
 18. |                   Angola   1998      6                |
 19. |      Antigua and Barbuda   1996      4     3       PF |
 20. |      Antigua and Barbuda   1997      4     3       PF |
 21. |      Antigua and Barbuda   1998      4                |
 22. |                Argentina   1996      2     3        F |
 23. |                Argentina   1997      2     3        F |
 24. |                Argentina   1998      3                |
 25. |                  Armenia   1996      5     4       PF |
 26. |                  Armenia   1997      5     4       PF |
 27. |                  Armenia   1998      4                |
 28. |                Australia   1996      1     1        F |
 29. |                Australia   1997      1     1        F |
 30. |                Australia   1998      1                |
 31. |                  Austria   1996      1     1        F |
 32. |                  Austria   1997      1     1        F |
 33. |                  Austria   1998      1                |
 34. |               Azerbaijan   1996      6     5       NF |
 35. |               Azerbaijan   1997      6     4       PF |
 36. |               Azerbaijan   1998      6                |
 37. |                  Bahamas   1996      1     2        F |
 38. |                  Bahamas   1997      1     2        F |
 39. |                  Bahamas   1998      1                |
 40. |                  Bahrain   1996      7     6       NF |
 41. |                  Bahrain   1997      7     6       NF |
 42. |                  Bahrain   1998      7                |
 43. |               Bangladesh   1996      2     4       PF |
 44. |               Bangladesh   1997      2     4       PF |
 45. |               Bangladesh   1998      2                |
 46. |                 Barbados   1996      1     1        F |
 47. |                 Barbados   1997      1     1        F |
 48. |                 Barbados   1998      1                |
 49. |                  Belarus   1996      6     6       NF |
 50. |                  Belarus   1997      6     6       NF |
 51. |                  Belarus   1998      6                |
 52. |                  Belgium   1996      1     2        F |
 53. |                  Belgium   1997      1     2        F |
 54. |                  Belgium   1998      1                |
 55. |                   Belize   1996      1     1        F |
 56. |                   Belize   1997      1     1        F |
 57. |                   Belize   1998      1                |
 58. |                    Benin   1996      2     2        F |
 59. |                    Benin   1997      2     2        F |
 60. |                    Benin   1998      2                |
 61. |                   Bhutan   1996      7     7       NF |
 62. |                   Bhutan   1997      7     7       NF |
 63. |                   Bhutan   1998      7                |
 64. |                  Bolivia   1996      2     3        F |
 65. |                  Bolivia   1997      1     3        F |
 66. |                  Bolivia   1998      1                |
 67. |   Bosnia and Herzegovina   1996      5     5       PF |
 68. |   Bosnia and Herzegovina   1997      5     5       PF |
 69. |   Bosnia and Herzegovina   1998      5                |
 70. |                 Botswana   1996      2     2        F |
 71. |                 Botswana   1997      2     2        F |
 72. |                 Botswana   1998      2                |
 73. |                   Brazil   1996      2     4       PF |
 74. |                   Brazil   1997      3     4       PF |
 75. |                   Brazil   1998      3                |
 76. |                   Brunei   1996      7     5       NF |
 77. |                   Brunei   1997      7     5       NF |
 78. |                   Brunei   1998      7                |
 79. |                 Bulgaria   1996      2     3        F |
 80. |                 Bulgaria   1997      2     3        F |
 81. |                 Bulgaria   1998      2                |
 82. |             Burkina Faso   1996      5     4       PF |
 83. |             Burkina Faso   1997      5     4       PF |
 84. |             Burkina Faso   1998      5                |
 85. |                  Burundi   1996      7     7       NF |
 86. |                  Burundi   1997      7     7       NF |
 87. |                  Burundi   1998      7                |
 88. |               Cabo Verde   1996      1     2        F |
 89. |               Cabo Verde   1997      1     2        F |
 90. |               Cabo Verde   1998      1                |
 91. |                 Cambodia   1996      6     6       NF |
 92. |                 Cambodia   1997      7     6       NF |
 93. |                 Cambodia   1998      6                |
 94. |                 Cameroon   1996      7     5       NF |
 95. |                 Cameroon   1997      7     5       NF |
 96. |                 Cameroon   1998      7                |
 97. |                   Canada   1996      1     1        F |
 98. |                   Canada   1997      1     1        F |
 99. |                   Canada   1998      1                |
100. | Central African Republic   1996      3     5       PF |
101. | Central African Republic   1997      3     5       PF |
102. | Central African Republic   1998      3                |
103. |                     Chad   1996      6     5       NF |
104. |                     Chad   1997      6     5       NF |
105. |                     Chad   1998      6                |
106. |                    Chile   1996      2     2        F |
107. |                    Chile   1997      2     2        F |
108. |                    Chile   1998      3                |
109. |                    China   1996      7     7       NF |
110. |                    China   1997      7     7       NF |
111. |                    China   1998      7                |
112. |                 Colombia   1996      4     4       PF |
113. |                 Colombia   1997      4     4       PF |
114. |                 Colombia   1998      3                |
115. |                  Comoros   1996      4     4       PF |
116. |                  Comoros   1997      5     4       PF |
117. |                  Comoros   1998      5                |
118. |      Congo (Brazzaville)   1996      4     4       PF |
119. |      Congo (Brazzaville)   1997      7     5       NF |
120. |      Congo (Brazzaville)   1998      7                |
121. |         Congo (Kinshasa)   1996      7     6       NF |
122. |         Congo (Kinshasa)   1997      7     6       NF |
123. |         Congo (Kinshasa)   1998      7                |
124. |               Costa Rica   1996      1     2        F |
125. |               Costa Rica   1997      1     2        F |
126. |               Costa Rica   1998      1                |
127. |            Cote d'Ivoire   1996      6     5       NF |
128. |            Cote d'Ivoire   1997      6     4       NF |
129. |            Cote d'Ivoire   1998      6                |
130. |                  Croatia   1996      4     4       PF |
131. |                  Croatia   1997      4     4       PF |
132. |                  Croatia   1998      4                |
133. |                     Cuba   1996      7     7       NF |
134. |                     Cuba   1997      7     7       NF |
135. |                     Cuba   1998      7                |
136. |                   Cyprus   1996      1     1        F |
137. |                   Cyprus   1997      1     1        F |
138. |                   Cyprus   1998      1                |
139. |           Czech Republic   1996      1     2        F |
140. |           Czech Republic   1997      1     2        F |
141. |           Czech Republic   1998      1                |
142. |           Czechoslovakia   1996      -     -        - |
143. |           Czechoslovakia   1997      -     -        - |
144. |           Czechoslovakia   1998      -                |
145. |                  Denmark   1996      1     1        F |
146. |                  Denmark   1997      1     1        F |
147. |                  Denmark   1998      1                |
148. |                 Djibouti   1996      5     6       NF |
149. |                 Djibouti   1997      5     6       NF |
150. |                 Djibouti   1998      5                |
151. |                 Dominica   1996      1     1        F |
152. |                 Dominica   1997      1     1        F |
153. |                 Dominica   1998      1                |
154. |       Dominican Republic   1996      3     3       PF |
155. |       Dominican Republic   1997      3     3       PF |
156. |       Dominican Republic   1998      2                |
157. |                  Ecuador   1996      2     4       PF |
158. |                  Ecuador   1997      3     3       PF |
159. |                  Ecuador   1998      2                |
160. |                    Egypt   1996      6     6       NF |
161. |                    Egypt   1997      6     6       NF |
162. |                    Egypt   1998      6                |
163. |              El Salvador   1996      3     3       PF |
164. |              El Salvador   1997      2     3        F |
165. |              El Salvador   1998      2                |
166. |        Equatorial Guinea   1996      7     7       NF |
167. |        Equatorial Guinea   1997      7     7       NF |
168. |        Equatorial Guinea   1998      7                |
169. |                  Eritrea   1996      6     4       PF |
170. |                  Eritrea   1997      6     4       PF |
171. |                  Eritrea   1998      6                |
172. |                  Estonia   1996      1     2        F |
173. |                  Estonia   1997      1     2        F |
174. |                  Estonia   1998      1                |
175. |                 Eswatini   1996      6     5       NF |
176. |                 Eswatini   1997      6     5       NF |
177. |                 Eswatini   1998      6                |
178. |                 Ethiopia   1996      4     5       PF |
179. |                 Ethiopia   1997      4     5       PF |
180. |                 Ethiopia   1998      4                |
181. |                     Fiji   1996      4     3       PF |
182. |                     Fiji   1997      4     3       PF |
183. |                     Fiji   1998      4                |
184. |                  Finland   1996      1     1        F |
185. |                  Finland   1997      1     1        F |
186. |                  Finland   1998      1                |
187. |                   France   1996      1     2        F |
188. |                   France   1997      1     2        F |
189. |                   France   1998      1                |
190. |                    Gabon   1996      5     4       PF |
191. |                    Gabon   1997      5     4       PF |
192. |                    Gabon   1998      5                |
193. |                  Georgia   1996      4     4       PF |
194. |                  Georgia   1997      3     4       PF |
195. |                  Georgia   1998      3                |
196. |                  Germany   1996      1     2        F |
197. |                  Germany   1997      1     2        F |
198. |                  Germany   1998      1                |
199. |             Germany, E.    1996      -     -        - |
200. |             Germany, E.    1997      -     -        - |
201. |             Germany, E.    1998      -                |
202. |             Germany, W.    1996      -     -        - |
203. |             Germany, W.    1997      -     -        - |
204. |             Germany, W.    1998      -                |
205. |                    Ghana   1996      3     4       PF |
206. |                    Ghana   1997      3     3       PF |
207. |                    Ghana   1998      3                |
208. |                   Greece   1996      1     3        F |
209. |                   Greece   1997      1     3        F |
210. |                   Greece   1998      1                |
211. |                  Grenada   1996      1     2        F |
212. |                  Grenada   1997      1     2        F |
213. |                  Grenada   1998      1                |
214. |                Guatemala   1996      3     4       PF |
215. |                Guatemala   1997      3     4       PF |
216. |                Guatemala   1998      3                |
217. |                   Guinea   1996      6     5       NF |
218. |                   Guinea   1997      6     5       NF |
219. |                   Guinea   1998      6                |
220. |            Guinea-Bissau   1996      3     4       PF |
221. |            Guinea-Bissau   1997      3     4       PF |
222. |            Guinea-Bissau   1998      3                |
223. |                   Guyana   1996      2     2        F |
224. |                   Guyana   1997      2     2        F |
225. |                   Guyana   1998      2                |
226. |                    Haiti   1996      4     5       PF |
227. |                    Haiti   1997      4     5       PF |
228. |                    Haiti   1998      5                |
229. |                 Honduras   1996      3     3       PF |
230. |                 Honduras   1997      2     3        F |
231. |                 Honduras   1998      2                |
232. |                  Hungary   1996      1     2        F |
233. |                  Hungary   1997      1     2        F |
234. |                  Hungary   1998      1                |
235. |                  Iceland   1996      1     1        F |
236. |                  Iceland   1997      1     1        F |
237. |                  Iceland   1998      1                |
238. |                    India   1996      2     4       PF |
239. |                    India   1997      2     4       PF |
240. |                    India   1998      2                |
241. |                Indonesia   1996      7     5       NF |
242. |                Indonesia   1997      7     5       NF |
243. |                Indonesia   1998      6                |
244. |                     Iran   1996      6     7       NF |
245. |                     Iran   1997      6     7       NF |
246. |                     Iran   1998      6                |
247. |                     Iraq   1996      7     7       NF |
248. |                     Iraq   1997      7     7       NF |
249. |                     Iraq   1998      7                |
250. |                  Ireland   1996      1     1        F |
251. |                  Ireland   1997      1     1        F |
252. |                  Ireland   1998      1                |
253. |                   Israel   1996      1     3        F |
254. |                   Israel   1997      1     3        F |
255. |                   Israel   1998      1                |
256. |                    Italy   1996      1     2        F |
257. |                    Italy   1997      1     2        F |
258. |                    Italy   1998      1                |
259. |                  Jamaica   1996      2     3        F |
260. |                  Jamaica   1997      2     3        F |
261. |                  Jamaica   1998      2                |
262. |                    Japan   1996      1     2        F |
263. |                    Japan   1997      1     2        F |
264. |                    Japan   1998      1                |
265. |                   Jordan   1996      4     4       PF |
266. |                   Jordan   1997      4     4       PF |
267. |                   Jordan   1998      4                |
268. |               Kazakhstan   1996      6     5       NF |
269. |               Kazakhstan   1997      6     5       NF |
270. |               Kazakhstan   1998      6                |
271. |                    Kenya   1996      7     6       NF |
272. |                    Kenya   1997      6     6       NF |
273. |                    Kenya   1998      6                |
274. |                 Kiribati   1996      1     1        F |
275. |                 Kiribati   1997      1     1        F |
276. |                 Kiribati   1998      1                |
277. |                   Kosovo   1996      -     -        - |
278. |                   Kosovo   1997      -     -        - |
279. |                   Kosovo   1998      -                |
280. |                   Kuwait   1996      5     5       PF |
281. |                   Kuwait   1997      5     5       PF |
282. |                   Kuwait   1998      5                |
283. |               Kyrgyzstan   1996      4     4       PF |
284. |               Kyrgyzstan   1997      4     4       PF |
285. |               Kyrgyzstan   1998      5                |
286. |                     Laos   1996      7     6       NF |
287. |                     Laos   1997      7     6       NF |
288. |                     Laos   1998      7                |
289. |                   Latvia   1996      2     2        F |
290. |                   Latvia   1997      1     2        F |
291. |                   Latvia   1998      1                |
292. |                  Lebanon   1996      6     5       NF |
293. |                  Lebanon   1997      6     5       NF |
294. |                  Lebanon   1998      6                |
295. |                  Lesotho   1996      4     4       PF |
296. |                  Lesotho   1997      4     4       PF |
297. |                  Lesotho   1998      4                |
298. |     Year(s) Under Review   1996   1996                |
299. |     Year(s) Under Review   1997   1997                |
300. |     Year(s) Under Review   1998   1998                |
     +-------------------------------------------------------+

.

[Cassie Wright]

EDIT: I have just realised the reason my code didn't work until I removed the missing observations that appeared after I attempted to reshape the data was because they were sat at the bottom. When I delete those observations at the beginning, I have no problem with running your code.

Hi Clyde, thank you very much for solving my problem! I had been stuck on this for almost two hours so I am very relieved that you took the time to help me out.

When I inputted your code with the dataex sample, and it worked perfectly. However, I tried it against my dataset, Stata threw me this error message:

. reshape long PR CL Status, i(country) j(year)
(j = 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2
> 013 2014 2015 2016 2017 2018 2019 2020 2021 2022)
variable id does not uniquely identify the observations
    Your data are currently wide. You are performing a reshape long. You specified
    i(country) and j(year). In the current wide form, variable country should uniquely
    identify the observations. Remember this picture:

         long                                wide
        +---------------+                   +------------------+
        | i   j   a   b |                   | i   a1 a2  b1 b2 |
        |---------------| <--- reshape ---> |------------------|
        | 1   1   1   2 |                   | 1   1   3   2  4 |
        | 1   2   3   4 |                   | 2   5   7   6  8 |
        | 2   1   5   6 |                   +------------------+
        | 2   2   7   8 |
        +---------------+
    Type reshape error for a list of the problem observations.

Upon inputting

reshape error

I got this:

reshape error
(j = 1996 1997 1998 1999 2000 2001 2002 2003 2004 2005 2006 2007 2008 2009 2010 2011 2012 2
> 013 2014 2015 2016 2017 2018 2019 2020 2021 2022)

i (country) indicates the top-level grouping such as subject id.

The data are currently in the wide form; there should be a single
observation per i.

118 of 323 observations have duplicate i values:

After seeing this, I browsed the data and saw that I had 1/118 missing observations come up. After I dropped them, I was able to achieve the same result as your example, and have long panel data.

I was wondering what you thought the issue might be? I suspect that I have done something wrong, as my dofile only runs if I include

reshape error

after the first reshape attempt. I'm not sure how but I have managed to end up with what I wanted by chance!

I understand it is probably vague for me to explain this without you seeing the actual data, so please let me know if you would like another dataex example.

[Cassie Wright]

Hi Andrew, sorry I didn't see your response when replying to Clyde because I hadn't refreshed the website. Thank you for your input and it works for me! Today is the first time I have ever handled Freedom House data so I wasn't sure what to do.

All the best,

Cassie

[Clyde Schechter]

my dofile only runs if I include Code:

reshape error
after the first reshape attempt.

I really don't know what to make of that. As far as I know, all -reshape error- does is give you an analysis of what is getting in the way of reshaping the data, but it does not actually fix any problems.

Certainly if you have a bunch of observations with no data, they will all be duplicates on the -i()- variable(s). So they need to be removed. To my mind, the important question is where did all those empty observations come from? This data appears to be imported from Excel. If it was brought in using the -import excel- command, I have seen that result in dragging in rows from the Excel sheet that come below the actual data. While -import excel- is supposed to be able to recognize where the data begins and ends, it does sometimes get it wrong and include extraneous empty rows. I have no idea why it does that in some spreadsheets and not others. My own approach when using -import excel- is to first manually inspect the spreadsheet and identify the range of rows and columns the data cover, and then use the -cellrange()- option of -import excel- to tell Stata where the data is and isn't.

If the data were brought into Stata by copy/paste, then it probably means you made a mousing error when you selected the data to copy. This happens with some frequency, which is why it really is not a good data practice and should be avoided. But a mousing error would be unlikely to result in selecting 118 extra rows--usually you end up one or two long or short of the mark (which is, in a way, worse, because 118 extra blank rows are almost certain to be noticed soon thereafter, whereas you could do your entire analysis with one or two rows missing and never even notice that your data are wrong.) So, if you have been using copy/paste to move data from Excel to Stata, I would say stop doing that: use -import excel- with the -cellrange()- option specified.

Other than that, I don't know what to make of this. If you can post a -dataex- that reproduces the phenomenon of needing to run -reshape- error to get it to run, I can try to figure it out.