Reshaping database with several variables transform into the values of one.

[Nicolas Maystre]

Dear Sir/Madam,

I have the initial database with the following variables:

country fdi2010 fdi2011 fdi2012 portfolio2010 portfolio2011 portfolio2012 otherinv2010 otherinv2011 otherinv2012

I would like to get the following one

country variables y_2010 y_2011 y_2012, where variables includes fdi portfolio and otherinv as value

Could you please tell me how to proceed. It should be easy but after having spent 2 hours trying and browsing the web, etc., I could not find the answer.

Many thanks for your support.

Best regards,

Nicolas

[Clyde Schechter]

I am not completely sure I understand what you are asking for as the end result. But I think this is what you need:

ds country, not
rename (`r(varlist)') y_=
reshape long y_@2010 y_@2011 y_@2012, i(country) j(variable) string

If this does not give what you wanted, when posting back please:

1. Post example data from your data set that can be used to develop and test code. Use the -dataex- command to do that. If you are running version 18, 17, 16 or a fully updated version 15.1 or 14.2, -dataex- is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.
2. Mock up by hand a table that looks like what you want for the end result--describing these things in words is somewhere between difficult and impossible.

[Nick Cox]

(essentially what Clyde Schechter said)

[Nicolas Maystre]

Dear Clyde Schechter and Nick Cox,

Thanks for your prompt response. Below is a better description of my dataset and question.

In fact my initial dataset (I skipped a step in my previous message) has the following structure.

and ideally I would like to get something like this

I am pretty sure that at some point I should reshape the dataset using the following command:
reshape wide fdi portfolio otherinv, i( unsubregionm49) j(year)"

but then I am stuck to get the final format I need.

Below are the dataex output you asked and attached my dataset in dta format, just in case. Many thanks for your support.

Best regards,

Nicolas

* Example generated by -dataex-. For more info, type help dataex
clear
input int year float(fdi otherinv portfolio) str31 unsubregionm49
2010   5874131456  -24364251136    8504007168 "Central Asia"                   
2010 175566635008  133937954816  -32880476160 "Eastern Asia"                   
2010 107303813120    3810746112  101058347008 "Latin America and the Caribbean"
2010   1789635712    1039947904      35652316 "Melanesia"                      
2010    -17222448     -51962476     2038990.8 "Micronesia"                     
2010  10412112896   -7399868928    7565708800 "Northern Africa"                
2010     31953058     199698976     -10664212 "Polynesia"                      
2010  41679056896  -11919619072    7508636160 "South-eastern Asia"             
2010  15576168448   2.82948e+10   37645615104 "Southern Asia"                  
2010  36056858624  -28885829632   -1130261504 "Sub-Saharan Africa"             
2010  13637578752    2224366080  -16464545792 "Western Asia"                   
2011  11029522432   -6087340544  -12863254528 "Central Asia"                   
2011 237747027968    2293041152   16439181312 "Eastern Asia"                   
2011  1.54927e+11   -3213479936   1.06214e+11 "Latin America and the Caribbean"
2011   1832311936      92320576     450150816 "Melanesia"                      
2011     -5420293     -21341306      47713352 "Micronesia"                     
2011   5238502400    5414950912  -11197093888 "Northern Africa"                
2011    127794520    -367851360     247757744 "Polynesia"                      
2011  32346406912  -48497983488   11151571968 "South-eastern Asia"             
2011  2.76176e+10   36171083776    2430509312 "Southern Asia"                  
2011   3.1184e+10  -15546456064   19223515136 "Sub-Saharan Africa"             
2011   6360793600  -97480859648  -22991847424 "Western Asia"                   
2012  13115804672    -476920832  -17381394432 "Central Asia"                   
2012 169765928960 -2.434792e+11   43102408704 "Eastern Asia"                   
2012 162172223488  -12922068992   8.44425e+10 "Latin America and the Caribbean"
2012   2872280064     7.472e+08     680527744 "Melanesia"                      
2012     32346440     -92600376      -3096212 "Micronesia"                     
2012   7967766016    8604433408   -2514095616 "Northern Africa"                
2012    116163152      64510232    -3496122.5 "Polynesia"                      
2012  50201382912   -4394414080  -43283148800 "South-eastern Asia"             
2012  18806978560   42770841600   3.15541e+10 "Southern Asia"                  
2012  39871131648  -20627165184   29301491712 "Sub-Saharan Africa"             
2012  15052997632 -110828429312   13892604928 "Western Asia"                   
2013   9623836672   -1219166848   -6028718592 "Central Asia"                   
2013 215017455616  1.108595e+11   -9499312128 "Eastern Asia"                   
2013 151052533760  -33895936000  100648960000 "Latin America and the Caribbean"
2013   2471883008    2888367360    -110248240 "Melanesia"                      
2013     45764616     -50383824     -11412360 "Micronesia"                     
2013  10809852928    6968636416    1096044928 "Northern Africa"                
2013     50728956    -247934144     -12416299 "Polynesia"                      
2013  45810073600  -23101958144  -59133935616 "South-eastern Asia"             
2013  30935848960   30256717824    8960190464 "Southern Asia"                  
2013  28093163520   13905196032   21932214272 "Sub-Saharan Africa"             
2013  -9325212672  -74086572032   -8989672448 "Western Asia"                   
2014   6035845632    1018030400   -1035313536 "Central Asia"                   
2014 135220133888 -272592240640   68741382144 "Eastern Asia"                   
2014 141897940992    6755573248  107674542080 "Latin America and the Caribbean"
2014   2041129728   -3507200512     354640640 "Melanesia"                      
2014    137157616    -223020208     1431803.3 "Micronesia"                     
2014  11328075776   10174476288     682262208 "Northern Africa"                
2014     62251788    -348858560      62184148 "Polynesia"                      
2014  37185249280  -38694408192  -48798449664 "South-eastern Asia"             
2014  28409696256    9242416128   44543254528 "Southern Asia"                  
2014  51467227136    2287559936    6864627712 "Sub-Saharan Africa"             
2014 -16797305856  -45003669504  -6.05174e+10 "Western Asia"                   
2015   5761179136   -1424752000    5759484416 "Central Asia"                   
2015 171371970560 -3.962587e+11 -2.036594e+11 "Eastern Asia"                   
2015 133230526464  -13594150912   5.14057e+10 "Latin America and the Caribbean"
2015   1481201536   -2821167872     165622208 "Melanesia"                      
2015     23809846     -26979648      -5484529 "Micronesia"                     
2015  1.10049e+10   20469401600    2060358528 "Northern Africa"                
2015     32998268    -304676896     -33793256 "Polynesia"                      
2015  56365756416  -51327455232  -74401595392 "South-eastern Asia"             
2015  42064441344   32838563840   11334703104 "Southern Asia"                  
2015  37942857728    -427235680   22556712960 "Sub-Saharan Africa"             
2015  -3189275392   18497671168  -69685878784 "Western Asia"                   
2016  16191769600   -6658324480    -750532480 "Central Asia"                   
2016  13794512896 -3.364059e+11 -115245817856 "Eastern Asia"                   
2016  1.27769e+11  -42809729024   46906724352 "Latin America and the Caribbean"
2016    858358016   -1773109632    -147341088 "Melanesia"                      
2016     58364592     -96197608     -17749456 "Micronesia"                     
2016  12313013248   32957782016    1794957952 "Northern Africa"                
2016     41090968    -357945152     -18871674 "Polynesia"                      
2016  59389026304  -8.02178e+10   -2248439040 "South-eastern Asia"             
2016  45541834752   -5125925376   -3768872192 "Southern Asia"                  
2016  27946129408   14386761728   15368549376 "Sub-Saharan Africa"             
2016  13515059200    7778136064    8637757440 "Western Asia"                   
2017   5575993344   -4331756544    5878394368 "Central Asia"                   
2017  52444942336    6163294720   53604130816 "Eastern Asia"                   
2017 1.259588e+11  -22420418560   42664808448 "Latin America and the Caribbean"
2017    -85919344   -2917049856    -383630432 "Melanesia"                      
2017     52438024    -120166488     -21259768 "Micronesia"                     
2017  12287194112    -257713600   23434913792 "Northern Africa"                
2017     -7604584      -6655156     -17468196 "Polynesia"                      
2017  82404933632  -53060608000   -4211501824 "South-eastern Asia"             
2017  34982318080   33240289280   33937514496 "Southern Asia"                  
2017  33931845632   39362248704   23703482368 "Sub-Saharan Africa"             
2017  -4097436160  -58616922112   24384958464 "Western Asia"                   
2018   6003874304   -3970576384   -2895965696 "Central Asia"                   
2018 1.180501e+11   -8853992448   32689147904 "Eastern Asia"                   
2018 146507169792   26938320896   17286295552 "Latin America and the Caribbean"
2018    255848736   -3539078656     469019200 "Melanesia"                      
2018     58483824      -8900964     -90535224 "Micronesia"                     
2018  13038130176   14980573184   -2148740096 "Northern Africa"                
2018     37235584      13569501     -29862658 "Polynesia"                      
2018  97266671616  -24134408192  -57996374016 "South-eastern Asia"             
2018  37133463552   56827969536   -9857965056 "Southern Asia"                  
2018  16682066944   22597922816    4942713344 "Sub-Saharan Africa"             
2018   -844659200  -7.79756e+10  -19703402496 "Western Asia"                   
2019   8743815168     967345408   -3820640768 "Central Asia"                   
end

[Joseph Coveney]

. . . I would like to get something like this
. . . and attached my dataset in dta format

Thanks. Try the following.

rename (fdi otherinv portfolio ) (y_=)
reshape long y_, i(unsubregionm49 year) j(variable) string
reshape wide y_, i(unsubregionm49 variable) j(year)

[Nicolas Maystre]

Many thanks Joseph Coveney . This works perfectly. I did not know about this particular command "rename (fdi otherinv portfolio ) (y_=)" and did not think about putting together "unsubregionm49 year" in the first reshape command under i(...). This is very clear and efficient. Wishing you a nice weekend.

[Nick Cox]

Note that search reshape leads to resources.

https://journals.sagepub.com/doi/pdf...867X0700700211 starts with the reverse of the problem here.

https://www.stata.com/support/faqs/d...-with-reshape/ makes the point that you may need two reshapes to get where you want to be.

[Clyde Schechter]

I'm directing this post not to O.P. but to the Statalist Forum audience generally.

Can I just emphasize the importance of showing example data, to get things done expeditiously. In #1, the description of the data set given is actually quite incorrect. It claims that there are variables y_2010, y_2011, and y_2012. When we finally get the example data, we find out that is not even close to true. Describing data sets in words is difficult even in the simplest circumstances, and it doesn't take much complexity to make words inadequate for the job no matter how hard we try. Had O.P. shown example data in #1, there would surely have been a correct response in #2, and his problem would have been solved in minutes, rather than over the span of two days.

While there are some situations where code can be developed without seeing the data, these are very much the exception. And, frankly, those who can identify those exceptional situations probably don't need help with developing their code. Whenever you seek help with code, you should show example data. Failing to do so just delays getting a helpful answer to your question and often wastes your time trying to adapt code that has no chance of working offered by somebody who wanted to help but wrote code for an imagined or incompletely/incorrectly described data set that is not actually the one in use.

[Clyde Schechter]

Correction of #8. As pointed out to me offline by Nick Cox, O.P. did designate y_2010, y_2011, and y_2012 as desired results, not variables he had. What I should have said in #8 is that O.P. stated he was starting with variables country fdi2010 fdi2011 fdi2012 portfolio2010 portfolio2011 portfolio2012 otherinv2010 otherinv2011 otherinv2012, but the example data later provided does show otherwise and this has the same implications. I had copied the wrong line in his post.

I'm sorry for any confusion caused by my error in #8, but it doesn't affect the overall thrust of what is said there.