Challenge with reshaping data.

[Bruce Peer]

Hello. How to I reshape the data below into a long form? I tried but I am doing something wrong. I though the stub was var. I tried reshape long var, i(country) j(j). Thank you.

* Example generated by -dataex-. For more info, type help dataex
clear
input str9 country str35 var2 str11(var3 var4 var5 var6 var7)
"Australia" "Current Account Balance as % of GDP" "-2.185"      "0.369"       "2.356"       "2.974"       "1.049"      
"Austria"   "Current Account Balance as % of GDP" "0.967"       "2.377"       "3.446"       "1.596"       "-0.237"     
"Belgium"   "Current Account Balance as % of GDP" "-0.867"      "0.098"       "1.371"       "1.364"       "-0.926"     
"Australia" "Exports"                             "257,225.100" "270,943.900" "250,119.500" "343,649.200" "411,857.400"
"Austria"   "Exports"                             "177,297.700" "171,860.300" "162,892.600" "195,762.500" "204,861.200"
"Belgium"   "Exports"                             "468,217.500" "446,915.400" "421,882.300" "549,253.900" "637,666.500"
"Australia" "Export Price Index"                  "110.700"     "124.700"     "121.100"     "155.200"     "203.700"    
"Austria"   "Export Price Index"                  "116.600"     "118.300"     "118.700"     "127.300"     "148.300"    
"Belgium"   "Export Price Index"                  "113.300"     "114.100"     "112.200"     "123.500"     "151.400"    
end

------------------ copy up to and including the previous line ------------------

Listed 9 out of 9 observations

[Mike Lacy]

The error message from your code gives explains the problem: The i() designation has to contain information that uniquely identifies an observation, and country doesn't do that. You have multiple observations with "Australia" and presumably the same prevalis for other countries. Country and var2 as i() will do this.

// Give var2 a name that reflects what it is, and which will exclude it from the stub.
rename var2 somelabel
reshape long var, i(country somelabel) j(j)
// I'm sure you want those numbers as numbers and not strings.
destring var, replace ignore(",")

[Scott Merryman]

I think you need to use two -reshape- commands.

gen id = var2
replace id = "CAB_GDP" if var2 == "Current Account Balance as % of GDP"
replace id = "XPI" if var2 == "Export Price Index"
drop var2
destring var* ,replace ignore(",")
reshape long var , i(country id) j(year) 
reshape wide var, i(country year) j(id) string
rename var* *
list

     +-----------------------------------------------+
     |   country   year   CAB_GDP    Exports     XPI |
     |-----------------------------------------------|
  1. | Australia      3    -2.185   257225.1   110.7 |
  2. | Australia      4      .369   270943.9   124.7 |
  3. | Australia      5     2.356   250119.5   121.1 |
  4. | Australia      6     2.974   343649.2   155.2 |
  5. | Australia      7     1.049   411857.4   203.7 |
     |-----------------------------------------------|
  6. |   Austria      3      .967   177297.7   116.6 |
  7. |   Austria      4     2.377   171860.3   118.3 |
  8. |   Austria      5     3.446   162892.6   118.7 |
  9. |   Austria      6     1.596   195762.5   127.3 |
 10. |   Austria      7     -.237   204861.2   148.3 |
     |-----------------------------------------------|
 11. |   Belgium      3     -.867   468217.5   113.3 |
 12. |   Belgium      4      .098   446915.4   114.1 |
 13. |   Belgium      5     1.371   421882.3   112.2 |
 14. |   Belgium      6     1.364   549253.9   123.5 |
 15. |   Belgium      7     -.926   637666.5   151.4 |

[Bruce Peer]

Thank you Scott and Mike. I learned a lot today! I appreciate your advice.