For each var reshaping, renaming and exporting to excel within a loop

[Marta Baltruszewicz]

Hello,

I have a dataset in a long format. I need to export each variable to a separate excel file (not sheet). Before exporting, each variable needs to be reshaped to a wide format (countries vertically and years horizontally). I also insert another loop to change the name of variables (to have 2000, 2001, 2003 etc instead of gov_exp2000 gov_exp2001 etc.) I have a code but it doesn't work. I get a message "nothing found where name expected". Help...

foreach x in gov_exp- health_insurance {
forval n = 2000/2020 {
      preserve 
        keep  Code Year `x'
    reshape wide `x' , i( Code ) j(Year)
        rename `x'`n' `n'
    export excel Code-2020 using "C:\Users\eemba\data_indicators\COVID_`x'.xlsx", firstrow(varlabels) replace
    restore 
}
}

* Example generated by -dataex-. To install: ssc install dataex
clear
input str4 Code double(Year gov_exp LifeLadder Standarddeviationofladderby StandarddeviationMeanofladde pop renewables unemploy_rate) float below_3_2_doll double(below_1_9_doll CO2tot) float health_insurance
"ABW" 2000                  .                  .                  .                  .    90853   .18    6.92         .           . .    .
"ABW" 2001                  .                  .                  .                  .    92898   .18     6.9         .           . .    .
"ABW" 2002                  .                  .                  .                  .    94992   .18       .         .           . .    .
"ABW" 2003                  .                  .                  .                  .    97017   .18       .         .           . . 99.2
"ABW" 2004                  .                  .                  .                  .    98737   .19       .         .           . .    .
"ABW" 2005                  .                  .                  .                  .   100031   .19       .         .           . .    .
"ABW" 2006                  .                  .                  .                  .   100834   .19       .         .           . .    .
"ABW" 2007                  .                  .                  .                  .   101222   .19    5.71         .           . .    .
"ABW" 2008                  .                  .                  .                  .   101358   .19       .         .           . .    .
"ABW" 2009                  .                  .                  .                  .   101455    .3       .         .           . .    .
"ABW" 2010  649162011.1731844                  .                  .                  .   101669  5.46    10.6         .           . .    .
"ABW" 2011                  .                  .                  .                  .   102046  5.66     8.9         .           . .    .
"ABW" 2012                  .                  .                  .                  .   102560  6.86       .         .           . .    .
"ABW" 2013                  .                  .                  .                  .   103159  6.89       .         .           . .    .
"ABW" 2014                  .                  .                  .                  .   103774  6.93       .         .           . .    .
"ABW" 2015                  .                  .                  .                  .   104341  6.73       .         .           . .    .
"ABW" 2016                  .                  .                  .                  .   104872   6.7       .         .           . .    .
"ABW" 2017                  .                  .                  .                  .   105366  6.46       .         .           . .    .
"ABW" 2018                  .                  .                  .                  .   105845     .       .         .           . .    .
"ABW" 2019                  .                  .                  .                  .   106314     .       .         .           . .    .
"ABW" 2020                  .                  .                  .                  .        .     .       .         .           . .    .
"AFG" 2000                  .                  .                  .                  . 20779953 54.24       .         .  64.8081056 .    .
"AFG" 2001                  .                  .                  .                  . 21606988 54.06       .         . 65.28821256 .    .
"AFG" 2002                  .                  .                  .                  . 22600770 43.77       .         . 63.51443743 .    .
"AFG" 2003                  .                  .                  .                  . 23680871 42.28       .         . 63.37016995 .    .
"AFG" 2004                  .                  .                  .                  . 24726684 49.84       .         . 63.57373522 .    .
"AFG" 2005                  .                  .                  .                  . 25654277 40.86       .         .  62.9228906 .    .
"AFG" 2006                  .                  .                  .                  . 26433049 37.14       .         . 62.13604779 .    .
"AFG" 2007                  .                  .                  .                  . 27100536 33.86       .         . 60.51280616 .    .
"AFG" 2008                  . 3.7235898971557617  1.774661898612976 .47659972310066223 27722276 21.34  2.4945         . 59.43158341 .    .
"AFG" 2009                  .  4.401778221130371 1.7226876020431519  .3913617432117462 28394813 17.85       .         . 56.47942902 .    .
"AFG" 2010  2284694323.144105  4.758380889892578 1.8786218166351318  .3948027491569519 29185507  14.9       .         . 54.31482746 .    .
"AFG" 2011                  .  3.831719160079956 1.7853597402572632 .46594223380088806 30117413 11.56       .         . 52.91669981 .    .
"AFG" 2012                  .  3.782937526702881 1.7982832193374634 .47536689043045044 31161376  14.4  1.6919         . 50.31140253 .    .
"AFG" 2013                  . 3.5721004009246826 1.2236899137496948  .3425687253475189 32269589 17.11       .         . 48.63242576 .    .
"AFG" 2014                  . 3.1308956146240234  1.395396113395691  .4456859230995178 33370794 20.36       .         . 47.13053004 .    .
"AFG" 2015                  . 3.9828546047210693 2.1606175899505615  .5424796342849731 34413603 19.96       .         . 45.73620722 .    .
"AFG" 2016                  .  4.220168590545654 1.7962194681167603  .4256274104118347 35383128 24.34       .         . 44.32309524 .    .
"AFG" 2017                  . 2.6617181301116943 1.4540507793426514  .5462827682495117 36296400 24.65 11.1836         . 42.56496454 .    .
"AFG" 2018                  .  2.694303274154663   1.40834379196167  .5227116942405701 37172386     .       .         . 41.43420816 .    .
"AFG" 2019                  .  2.375091791152954 1.4129045009613037  .5948841571807861 38041754     .       .         . 40.11020013 .    .
"AFG" 2020                  .                  .                  .                  .        .     .       .         . 38.75350603 .    .
"AGO" 2000                  .                  .                  .                  . 16395473 73.44       .         . 28.35087993 .    .
"AGO" 2001                  .                  .                  .                  . 16945753 72.34       .         . 27.65551275 .    .
"AGO" 2002  9732995769.260946                  .                  .                  . 17519417 70.42       .  .5595577 25.66328552 .    .
"AGO" 2003 10121342300.454456                  .                  .                  . 18121479 65.14       .         . 24.89883808 .    .
"AGO" 2004 10333890491.302538                  .                  .                  . 18758145 61.92 23.6427         . 23.57044761 .    .
"AGO" 2005 11188503230.027456                  .                  .                  . 19433602 68.38       .  .5693087 23.48667006 .    .
"AGO" 2006 12287214252.767294                  .                  .                  . 20149901 62.13       .         . 23.89033661 .    .
"AGO" 2007 13331627458.785486                  .                  .                  . 20905363 58.49       .         . 24.24531185 .    .
"AGO" 2008 14976750287.733315                  .                  .                  . 21695634 54.88       .  .5380489 25.33584475 .    .
"AGO" 2009  13593541968.15867                  .                  .                  . 22514281 52.41  3.7823         .  26.6927585 .    .
"AGO" 2010 14281398413.808937                  .                  .                  . 23356246 50.76    9.43  .5708045 26.12416448 .    .
"AGO" 2011  18170887460.09577  5.589000701904297 2.2124738693237305  .3958621621131897 24220661 49.24  7.3622  .5788268 29.40445833 .    .
"AGO" 2012  18283962487.46051  4.360249996185303 2.1507461071014404  .4932621121406555 25107931 49.44       .  .5758446 29.21615372 .    .
"AGO" 2013  22193536140.77051 3.9371068477630615  2.325728178024292  .5907201170921326 26015780 47.94       .  .5804921 28.93276953 .    .
"AGO" 2014  19055640807.51551 3.7948379516601562  2.196711301803589  .5788682699203491 26941779 48.21       .         . 28.64982238 .    .
"AGO" 2015   17382324358.9827                  .                  .                  . 27884381 47.82       .  .6050247 28.45810444 .    .
"AGO" 2016 15552189668.827925                  .                  .                  . 28842484 49.01       .         . 28.62303072 .    .
"AGO" 2017 15594812883.592419                  .                  .                  . 29816748 56.16       .         . 28.62505016 .    .
"AGO" 2018 15328357183.741985                  .                  .                  . 30809762     .       .  .6932878 28.55959616 .    .
"AGO" 2019                  .                  .                  .                  . 31825295     .       .         . 28.34001032 .    .
"AGO" 2020                  .                  .                  .                  .        .     .       .         . 27.97234141 .    .
"AIA" 2000                  .                  .                  .                  .        .   .19       .         .           . .    .
"AIA" 2001                  .                  .                  .                  .        .   .18    6.56         .           . .    .
"AIA" 2002                  .                  .                  .                  .        .   .19     7.8         .           . .    .
"AIA" 2003                  .                  .                  .                  .        .   .17       .         .           . .    .
"AIA" 2004                  .                  .                  .                  .        .   .14       .         .           . .    .
"AIA" 2005                  .                  .                  .                  .        .   .14       .         .           . .    .
"AIA" 2006                  .                  .                  .                  .        .   .12       .         .           . .    .
"AIA" 2007                  .                  .                  .                  .        .   .12       .         .           . .    .
"AIA" 2008                  .                  .                  .                  .        .   .12       .         .           . .    .
"AIA" 2009                  .                  .                  .                  .        .   .12       .         .           . .    .
"AIA" 2010                  .                  .                  .                  .        .   .12       .         .           . .    .
"AIA" 2011                  .                  .                  .                  .        .   .13       .         .           . .    .
"AIA" 2012                  .                  .                  .                  .        .   .13       .         .           . .    .
"AIA" 2013                  .                  .                  .                  .        .   .14       .         .           . .    .
"AIA" 2014                  .                  .                  .                  .        .   .13       .         .           . .    .
"AIA" 2015                  .                  .                  .                  .        .   .15       .         .           . .    .
"AIA" 2016                  .                  .                  .                  .        .   .21       .         .           . .    .
"AIA" 2017                  .                  .                  .                  .        .   .18       .         .           . .    .
"AIA" 2018                  .                  .                  .                  .        .     .       .         .           . .    .
"AIA" 2019                  .                  .                  .                  .        .     .       .         .           . .    .
"AIA" 2020                  .                  .                  .                  .        .     .       .         .           . .    .
"ALB" 2000 474145773.61126804                  .                  .                  .  3089027 41.45    16.8         .  .544056149 .    .
"ALB" 2001  572343042.8961098                  .                  .                  .  3060173 39.13    16.4         .  .581210702 .    .
"ALB" 2002   677285130.269561                  .                  .                  .  3051010  35.9    15.8  .1698068  .896235294 .    .
"ALB" 2003  737891843.9792208                  .                  .                  .  3039616 33.75      15         .  .605207993 .    .
"ALB" 2004  842780231.7788218                  .                  .                  .  3026939 35.93    14.4         .  .411587693 .    .
"ALB" 2005  874861084.8267231                  .                  .                  .  3011487 36.87    14.1  .1092852  .280360014 .    .
"ALB" 2006  929668113.0498841                  .                  .                  .  2992547 31.71    13.8         .  .182697484 .    .
"ALB" 2007 1020003585.6806593  4.634251594543457 1.7649469375610352 .38084831833839417  2970017  32.1 15.9663         .  .121777541 .    .
"ALB" 2008 1134042588.0875955                  .                  .                  .  2947314 35.91 13.0599 .06545949  .092654162 . 16.8
"ALB" 2009 1249681873.6788833  5.485469818115234 1.9196404218673706 .34995004534721375  2927519 37.22 13.6739         .  .086773262 .    .
"ALB" 2010 1330729761.4798238  5.268936634063721   1.86220121383667  .3534301817417145  2913021 37.11  14.086 .06766825  .082655328 .    .
"ALB" 2011  1339397922.004451  5.867421627044678 1.8457833528518677  .3145816922187805  2905195 35.96 13.4809 .07004389   .13994216 .    .
"ALB" 2012 1340775683.6613057  5.510124206542969  1.921202540397644  .3486677408218384  2900401 40.05 13.3759 .07671665  .265361681 .    .
"ALB" 2013 1379752099.1141727  4.550647735595703  2.315580129623413  .5088462829589844  2895092 41.29 15.8659  .1130014  .156319415 .    .
"ALB" 2014 1467981088.4894369   4.81376314163208  2.660069465637207  .5525966882705689  2889104 38.69 18.0548         .  .125221338 .    .
"ALB" 2015 1451774878.3634658 4.6066508293151855 2.7290008068084717  .5924045443534851  2880703 38.61  17.193  .1026211  .111281772 .    .
end

[Joro Kolev]

The code does not work because variable names cannot be just a number in Stata.

[cu dao huy]

Hi Marta,

Your forval loop have an issue, so instead of using it after foreach, you should use it after "keep Code year `x'".

And if you want your excel file have "2000" instead of "gov_exp2000" with option firstrow(varlabels) you don't need to rename variables (needed if you use optioni firstrow(variables) ). Just change variables label. For example:
cap la var `x'2000 "2000"
....
cap la var `x'2020 "2020"

I used "cap" because maybe some code missing year.

Hope this help!

[Nick Cox]

 
 foreach x in gov_exp- health_insurance

should be

 
 foreach x of var gov_exp-health_insurance

if I understand what you want. But is your long-term goal really one row of data in each of many, many worksheets?

Just in case it's helpful for your purpose, this reshapes everything wide (which from a Stata point of view only makes sense if it is needed for some other program);

rename (Standarddeviation*) (SD*)
ds Code Year, not 
reshape wide `r(varlist)' , i(Code) j(Year)

[Marta Baltruszewicz]

Hello again,

It is so nice to wake up to some answers!
Nick - I agree that the wide format is not ideal. My colleague wants to use data in a different program and he insisted on having each indicator in a separate spreadsheet.

I combined all of your answers and figured out a code that is finally working. Thank you for all your input lovely STATA people!

foreach x of var gov_exp- health_insurance {
      preserve 
        keep  Code Year `x'
    reshape wide `x' , i( Code ) j(Year)
    forval n = 2000/2020 {
    label var `x'`n' "`n'"
    }
            export excel Code-`x'2020 using "C:\Users\eemba\OneDrive - University of Leeds\work_leeds\COVID-tracker\data_indicators\all_selected_indicators\COVID_`x'.xlsx", firstrow(varlabels) replace
    restore 
}