Reshape Data Using reshape twice

[Sultan Mehmood]

Dear all,


I have been trying to solve the following problem: I want to convert data in (country-year) panel data format to conduct panel analysis.

My raw data is as follows:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str52 CountryName str3 CountryCode str131 IndicatorName str25 IndicatorCode double(YR1960 YR1961 YR1962)
"Early-demographic dividend" "EAR" "Air transport, freight (million ton-km)"                                                               "IS.AIR.GOOD.MT.K1"                 .                  .                 .
"Early-demographic dividend" "EAR" "Air transport, passengers carried"                                                                     "IS.AIR.PSGR"                       .                  .                 .
"Early-demographic dividend" "EAR" "Air transport, registered carrier departures worldwide"                                                "IS.AIR.DPRT"                       .                  .                 .
"Early-demographic dividend" "EAR" "All education staff compensation, primary (% of total expenditure in primary public institutions)"     "SE.XPD.MPRM.ZS"                    .                  .                 .
"Early-demographic dividend" "EAR" "All education staff compensation, secondary (% of total expenditure in secondary public institutions)" "SE.XPD.MSEC.ZS"                    .                  .                 .
"Early-demographic dividend" "EAR" "All education staff compensation, tertiary (% of total expenditure in tertiary public institutions)"   "SE.XPD.MTER.ZS"                    .                  .                 .
"Early-demographic dividend" "EAR" "All education staff compensation, total (% of total expenditure in public institutions)"               "SE.XPD.MTOT.ZS"                    .                  .                 .
"Early-demographic dividend" "EAR" "Alternative and nuclear energy (% of total energy use)"                                                "EG.USE.COMM.CL.ZS" .8061091873266525 1.0026897227851863 .8154736857379168
"Early-demographic dividend" "EAR" "Annual freshwater withdrawals, agriculture (% of total freshwater withdrawal)"                         "ER.H2O.FWAG.ZS"                    .                  .                 .
"Early-demographic dividend" "EAR" "Annual freshwater withdrawals, domestic (% of total freshwater withdrawal)"                            "ER.H2O.FWDM.ZS"                    .                  .                 .
end

Now, I use Baum and Cox (2007, Stata tip 45: Getting those data into shape) and try to reshape twice. This is what I try:

reshape long YR, i(CountryName IndicatorName)
rename _j year
reshape wide YR, i(CountryName year) j(IndicatorName IndicatorCode) string

However, I get the error "variable IndicatorCode not constant within CountryName year". But my first reshape works and I am left with the following data before the code fails:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str52 CountryName str131 IndicatorName int year str3 CountryCode str25 IndicatorCode double YR
"Bermuda" "GDP per capita (current US$)"     2012 "BMU" "NY.GDP.PCAP.CD"     85458.45550788604
"Bermuda" "GDP per capita (current US$)"     2013 "BMU" "NY.GDP.PCAP.CD"     85748.06541437825
"Bermuda" "GDP per capita (current US$)"     2014 "BMU" "NY.GDP.PCAP.CD"                     .
"Bermuda" "GDP per capita (current US$)"     2015 "BMU" "NY.GDP.PCAP.CD"                     .
"Bermuda" "GDP per capita (current US$)"     2016 "BMU" "NY.GDP.PCAP.CD"                     .
"Bermuda" "GDP per capita growth (annual %)" 1960 "BMU" "NY.GDP.PCAP.KD.ZG"                  .
"Bermuda" "GDP per capita growth (annual %)" 1961 "BMU" "NY.GDP.PCAP.KD.ZG" 2.1492747252721074
"Bermuda" "GDP per capita growth (annual %)" 1962 "BMU" "NY.GDP.PCAP.KD.ZG" 2.0039699570851326
end

Now, how can I get country-year variables with all indicators in columns as in a normal country-year panel with multiple variables in the columns from ABOVE data?

I have tried many many variations in the reshape command but to no avail. Therefore, if any one of you can help me it will be greatly appreciated. Thanks in advance.

Kind Regards,
Sultan

Full Reference
Baum, C. F., & Cox, N. J. (2007). Stata tip 45: Getting those data into shape. Stata Journal, 7(2), 268-271.

[Clyde Schechter]

So, to be honest, I'm having trouble imagining what you want the end result to be here. Could you hand-work it for a short example and show that?

All of that said, you cannot use more than one variable in the -j()- option of -reshape-. Also, at least in your example data, there is a one-to-one correspondence between indicator codes and indicator names, so the usual way out of this, using -egen, group()- to create a variable that indexes all pairs of indicator countires and names, would make no sense.

Also, bear in mind that the values of whatever variable does end up in -j()- end up being parts of the names of the newly created variables. As such, these values must all be legal as parts of variable names. Of the variables in your example, only year and country code qualify: the rest all contain embedded spaces or embedded periods(.) or other special characters. Clearly you don't want to use year for the purpose, as it would simply undo the earlier -reshape-. In any case, whatever variable you ultimately do want to put in j(), if it is to derive from something you've shown us, has to be cleaned up with the -strtoname()- function first.

Finally, I'll just note that most Stata commands are most friendly to fully-long data layouts. So it wouldn't surprise me to find out that if you do figure out what your -reshape wide- command is supposed to be, you will come to regret doing it in the first place.

[William Lisowski]

This is a restatement of the problem previously discussed, and subsequently addressed, in https://www.statalist.org/forums/for...peated-id-data