Export to single Excel file with multiple sheets (based on variables)

[Budu Gulo]

 +---------------------------------------------------------------------------------------------------------+
  | country   year     NGDPRPC     NGDPDPC   NGSD_N~P   PCPIPCH   rank~RPC   rank~DPC   rank_N~P   rank_P~H |
  |---------------------------------------------------------------------------------------------------------|
  |  Greece   2010   20327.829   26972.873      5.664     4.704          2          2          4          1 |
  +---------------------------------------------------------------------------------------------------------+

In my data set, each "main" variable e.g. NGDPRPC has an associated rank variable. I would like to export the data into single excel file with 4 sheets (there are 4 "major" variables in this example) ; each sheet will contain name, year, and one "major" variable with its rank variable i.e. total four variables. In addition, I would like to name the sheets according to the "major" variables they contain.

* Example generated by -dataex-. To install: ssc install dataex
clear
input str32 country int year double(NGDPRPC NGDPDPC NGSD_NGDP PCPIPCH) float(rank_NGDPRPC rank_NGDPDPC rank_NGSD_NGDP rank_PCPIPCH)
"Greece"  2010 20327.829 26972.873  5.664  4.704 2 2 4 1
"Estonia" 2010  11052.81 14672.328 23.075  2.741 4 4 1 2
"Malta"   2010 15939.801 21150.423 18.954  2.041 3 3 2 4
"Cyprus"  2010 20490.637 31261.236 12.558  2.558 1 1 3 3
"Estonia" 2011 11928.681 17470.842 26.442   5.08 4 4 1 1
"Malta"   2011 16113.851 22933.749 19.254  2.513 3 3 2 4
"Cyprus"  2011  20051.42 32692.698 14.706  3.481 1 1 3 2
"Greece"  2011 18464.601 25896.934  5.099  3.118 2 2 4 3
"Estonia" 2012 12487.099  17431.54 27.143  4.219 4 4 1 1
"Cyprus"  2012 18936.417 29066.268  10.16  3.089 1 1 3 3
"Greece"  2012  17173.71 22171.911  8.972  1.035 2 2 4 4
"Malta"   2012 16450.882  22069.52 19.718  3.227 3 3 2 2
"Estonia" 2013  12774.29 19078.213 27.426  3.247 4 4 1 1
"Greece"  2013  16742.04 21805.257   9.56  -.854 3 3 3 4
"Cyprus"  2013 17732.983  27825.87  8.246   .381 1 1 4 3
"Malta"   2013 17005.342 24021.743 21.781   .979 2 2 2 2
"Cyprus"  2014 17644.755 27267.428  8.022  -.268 2 1 4 3
"Estonia" 2014 13170.071 19969.128 26.937   .476 4 4 1 2
"Greece"  2014 16984.502  21726.89  10.28 -1.394 3 3 3 4
"Malta"   2014 18097.074 26202.597 26.674   .771 1 2 2 1
end

[Mike Lacy]

Following -help export excel-, I arrive at the following:

foreach v of varlist NGDPRPC NGDPDPC NGSD_NGDP PCPIPCH {
   export excel `v' rank_`v' using c:/temp/crap, sheet("`v'") firstrow(variables)
}

[Budu Gulo]

Mike Lacy Thanks a lot! Your code does the job brilliantly. I mistakenly asked for "name" instead of "country"; just for future reference I am posting the complete code that generates sheets with four variables

foreach v of varlist NGDPRPC NGDPDPC NGSD_NGDP PCPIPCH {
   export excel country year `v' rank_`v' using c:/temp/crap, sheet("`v'") firstrow(variables) 
}

Another request : Would it be possible to have sheets with "year" and "rank_*" sorted?

[Mike Lacy]

If your data is sorted by year, it can't be simultaneously sorted by rank_. It could be sorted by year, and within year, sorted by rank. Take a look and -help sort-, try some things, and see what happens.