Reshape wide

[abucamara]

I need help on "reshape wide". I used the below code but the reshape does not work fine.

//import data
import delimited "FAOSTAT_data_5-19-2022.csv", clear


//run the country code
do fao_country_groupings.do

//code the required variables
rename year year1
gen year=yearcode
tab year,m

//convert value to numeric
destring value, gen(value1) ignore(<)

//collapse
keep if itemcode==210081
save mod_severe_mil.dta, replace

//drop china to remove duplicates
duplicates tag country_all,gen(dup)
tab dup,m
//encode year
encode year1, gen(year2)
tab year2,m

//duplicates drop
duplicates drop country_all year2,force

//create unique ID
egen id=group(country_all year2)

//reshape wide
reshape wide value1, i(id) j(year2)



clear
input double(country_all country year) long year2 doublevalue1
1 1 20142016 1 15.5
1 1 20152017 2 17.6
1 1 20162018 3 19.7
1 1 20172019 4 21.4
1 1 20182020 5 24
2 2 20142016 1 1.1
2 2 20152017 2 1.1
2 2 20162018 3 1.1
2 2 20172019 4 1.1
2 2 20182020 5 1
3 3 20142016 1 9.1
3 3 20152017 2 8.7
3 3 20162018 3 8.2
3 3 20172019 4 7.4
3 3 20182020 5 7.6
101 99 20142016 1 .
101 99 20152017 2 .
101 99 20162018 3 .
101 99 20172019 4 .
101 99 20182020 5 .
102 99 20142016 1 .
102 99 20152017 2 .
102 99 20162018 3 .
102 99 20172019 4 .
102 99 20182020 5 .
103 99 20142016 1 18.5
103 99 20152017 2 .
103 99 20162018 3 .
103 99 20172019 4 21.6
103 99 20182020 5 23.4
106 99 20142016 1 .
106 99 20152017 2 .
106 99 20162018 3 .
106 99 20172019 4 .
106 99 20182020 5 .
107 99 20142016 1 8.3
107 99 20152017 2 11.3
107 99 20162018 3 14.2
107 99 20172019 4 15.9
107 99 20182020 5 16
108 99 20142016 1 .
108 99 20152017 2 .
108 99 20162018 3 .5
108 99 20172019 4 .4
108 99 20182020 5 .4
110 99 20142016 1 2.6
110 99 20152017 2 2.9
110 99 20162018 3 3.1
110 99 20172019 4 3.3
110 99 20182020 5 3.1
111 99 20142016 1 .5
111 99 20152017 2 .5
111 99 20162018 3 .4
111 99 20172019 4 .3
111 99 20182020 5 .3
4 4 20142016 1 .6
4 4 20152017 2 .7
4 4 20162018 3 .8
4 4 20172019 4 1
4 4 20182020 5 .9
112 99 20142016 1 .
112 99 20152017 2 .
112 99 20162018 3 .
112 99 20172019 4 .
112 99 20182020 5 .
5 5 20142016 1 .
5 5 20152017 2 .
5 5 20162018 3 .
5 5 20172019 4 .
5 5 20182020 5 .
6 6 20142016 1 50.4
6 6 20152017 2 50.3
6 6 20162018 3 50.2
6 6 20172019 4 50.8
6 6 20182020 5 52
113 99 20142016 1 .
113 99 20152017 2 .
113 99 20162018 3 .
113 99 20172019 4 .
113 99 20182020 5 .
114 99 20142016 1 .
114 99 20152017 2 .
114 99 20162018 3 .
114 99 20172019 4 .
114 99 20182020 5 .
115 99 20142016 1 .
115 99 20152017 2 .
115 99 20162018 3 .
115 99 20172019 4 .
115 99 20182020 5 .4
116 99 20142016 1 .
116 99 20152017 2 .
116 99 20162018 3 .
116 99 20172019 4 .
116 99 20182020 5 .
7 7 20142016 1 .
7 7 20152017 2 .
7 7 20162018 3 .
7 7 20172019 4 .
7 7 20182020 5 .
end
label values country_all country_all
label def country_all 1 "Afghanistan", modify
label def country_all 2 "Albania", modify
label def country_all 3 "Algeria", modify
label def country_all 4 "Azerbaijan", modify
label def country_all 5 "Bahrain", modify
label def country_all 6 "Bangladesh", modify
label def country_all 7 "Benin", modify
label def country_all 101 "American Samoa", modify
label def country_all 102 "Andorra", modify
label def country_all 103 "Angola", modify
label def country_all 106 "Antigua and Barbuda", modify
label def country_all 107 "Argentina", modify
label def country_all 108 "Armenia", modify
label def country_all 110 "Australia", modify
label def country_all 111 "Austria", modify
label def country_all 112 "Bahamas (the)", modify
label def country_all 113 "Barbados", modify
label def country_all 114 "Belarus", modify
label def country_all 115 "Belgium", modify
label def country_all 116 "Belize", modify
label values country country
label def country 1 "Afghanistan", modify
label def country 2 "Albania", modify
label def country 3 "Algeria", modify
label def country 4 "Azerbaijan", modify
label def country 5 "Bahrain", modify
label def country 6 "Bangladesh", modify
label def country 7 "Benin", modify
label def country 99 "Non-member-Countries", modify
label values year2 year2
label def year2 1 "2014-2016", modify
label def year2 2 "2015-2017", modify
label def year2 3 "2016-2018", modify
label def year2 4 "2017-2019", modify
label def year2 5 "2018-2020", modify
[/CODE]

[Ken Chui]

What is that you're trying to achieve? The code here created a unique id by country and year:

//create unique ID
egen id=group(country_all year2)

And yet, the reshape command still treats the data as if it's long form repeated by year2:

//reshape wide
reshape wide value1, i(id) j(year2)

So, the data simply just spread apart into five variables. If you wish to have the different years on one single line, try:

drop id year
reshape wide value1, i(country_all) j(year2)

[abucamara]

Thanks Ken. The last code works perfect!.