Building a complicated table in STATA

[Marta Baltruszewicz]

Hello,

I'm trying to build three tables (each one for a country based on var country_id) in excel.
Rows should refer to achievement or not of a wellbeing outcome (clean_drink_water, basic_edu_both, adeq_food) and to a number of observations in each group.
Columns refer to income deciles.
The inside of the table is an average share (%) of having clean fuels in each income group. Here is an example of how it should look like:

I can get produce results for each category (safe water, adeq food and basic edu) by collapsing data, for example:

 preserve  collapse (mean) share_clean_fuels (count)num_obs  [weight=wt_hh], by ( country_id adeq_food inc_decile_wght_hh)
xpose,clear

but this produces a wide table for all countries, and I have to reshuffle it manually to get to the format like on the pic.
So:
1) Is there a way to append results from stata to an existing excel sheet by specifying columns and rows? So I could collapse data and export to an excel sheet, and then restore and collapse again and add to the already existing sheet in the excel.

2) is it possible to build a table in stata that looks like the one above? which function should I use?

* Example generated by -dataex-. To install: ssc install dataex
clear
input float(country_id inc_decile_wght_hh) double wt_hh float(share_clean_fuels clean_drink_water adeq_food basic_edu_both num_obs)
1  1  594.6710205078125 1.1096567 1 1 0 1
1  1 1357.1649169921875       100 1 1 1 1
2  1 293.05743408203125 .13516809 0 1 0 1
2  1    621.49072265625  .9239903 1 1 0 1
3  1     709.1865234375  2.331698 1 1 0 1
3  1  709.3440551757813       100 1 1 0 1
2  1    621.49072265625  .5868586 1 1 0 1
1  2 1357.1649169921875 2.3599381 1 0 0 1
1  2 1357.1649169921875  .7133285 1 1 0 1
2  2  280.3486022949219 1.2994117 0 1 0 1
2  2    621.49072265625 .14939886 1 1 1 1
3  2     709.1865234375  .6814974 1 1 0 1
3  3    621.49072265625  .8172332 1 1 1 1
2  3     709.1865234375  1.523302 1 1 1 1
1  3    621.49072265625  1.496123 1 1 0 1
3  3 293.05743408203125  2.638437 1 1 0 1
2  3  280.3486022949219   2.58706 1 1 0 1
1  3    621.49072265625  .5164462 1 1 0 1
2  3  280.3486022949219       100 1 1 0 1
1  4  709.3440551757813  .4212497 1 1 0 1
1  4    621.49072265625  .9786306 1 1 0 1
1  4    621.49072265625   .879786 1 1 0 1
2  4 1357.1649169921875       100 1 1 1 1
2  4     709.1865234375  5.002633 1 1 0 1
2  4  594.6710815429688  8.831465 1 1 1 1
3  4    621.49072265625       100 1 1 1 1
3  4  292.9923400878906  .2703528 0 1 0 1
3  4    621.49072265625  .4348797 1 1 1 1
1  4 1357.1649169921875  2.162031 1 1 0 1
2  4     709.1865234375       100 0 1 0 1
1  5    621.49072265625 .39879015 0 1 0 1
1  5 1357.1649169921875 18.288853 1 1 0 1
1  5    621.49072265625 1.1613582 1 1 0 1
2  5  709.3441162109375   8.61513 1 1 0 1
2  5     709.1865234375       100 1 1 0 1
2  5  678.5824584960938 .50563604 1 1 0 1
3  5  709.3440551757813       100 1 1 0 1
3  5 1357.1649169921875 .48106995 1 1 0 1
3  5    621.49072265625 1.4160714 1 1 0 1
1  5  292.9923400878906       100 1 1 1 1
1  5  709.1865844726563   44.9127 1 1 0 1
1  6  594.6710815429688  2.948138 1 1 0 1
1  6     709.1865234375 3.7826114 1 1 0 1
1  6 293.05743408203125  5.097307 1 1 1 1
2  6     709.1865234375  2.161576 1 1 0 1
2  6  709.1865844726563  .4804441 1 0 0 1
2  6  594.6710205078125  2.185974 1 1 0 1
2  6 1357.1649169921875  .7953057 1 1 0 1
3  6     709.1865234375 14.915644 1 1 0 1
3  6  709.3440551757813  .3847249 1 1 0 1
3  6     709.1865234375 1.1050651 1 1 0 1
3  6     709.1865234375 2.0540721 1 1 0 1
1  6  280.3486022949219  .6729252 1 1 0 1
1  6 1357.1649169921875       100 1 1 0 1
1  7  709.3440551757813  20.40264 1 0 0 1
1  7  709.3441162109375  .3755092 1 1 0 1
1  7     709.1865234375 1.0896412 1 0 0 1
1  7     709.1865234375  1.837703 1 1 0 1
2  7  678.5824584960938       100 1 1 0 1
2  7  709.1865844726563 18.756695 1 1 1 1
2  7     709.1865234375  35.05869 1 1 1 1
2  7     709.1865234375  .4216659 1 0 0 1
2  7  292.9923400878906  1.845998 1 1 0 1
3  7  594.6710205078125  .7204735 1 1 0 1
3  8  709.3441162109375  3.522251 1 0 0 1
3  8  292.9923400878906       100 0 1 0 1
3  8  709.1865844726563  3.384126 1 1 0 1
1  8  292.9923400878906  2.721675 0 1 0 1
1  8 293.05743408203125 10.114495 1 1 0 1
1  8  709.3441162109375       100 1 1 1 1
2  8 1357.1649169921875       100 1 1 1 1
2  8 1357.1649169921875       100 1 1 0 1
2  8  709.3441162109375  4.841481 1 1 0 1
3  8  709.1865844726563  9.268721 1 1 1 1
3  8  292.9923400878906  43.59368 0 1 1 1
3  8  709.3440551757813  1.288522 1 0 1 1
1  8    621.49072265625  10.10629 0 1 1 1
2  8  709.3440551757813  42.56044 1 1 0 1
1  8     709.1865234375       100 1 1 1 1
1  9    621.49072265625       100 1 1 1 1
1  9 293.05743408203125 2.1032376 0 1 0 1
1  9    621.49072265625   9.39954 1 1 0 1
2  9  709.3440551757813       100 1 1 1 1
2  9  709.3440551757813  7.307757 1 1 0 1
2  9  293.0574035644531 13.016968 1 1 0 1
3  9  709.3440551757813       100 1 1 1 1
3  9     709.1865234375 1.4885077 1 1 0 1
3  9  709.3440551757813       100 1 1 0 1
1 10    621.49072265625  2.721675 1 1 0 1
1 10  709.3441162109375  29.13664 1 1 1 1
1 10  678.5824584960938       100 1 1 0 1
2 10     709.1865234375  46.91713 1 1 0 1
2 10    621.49072265625  2.723702 1 1 0 1
2 10  709.3440551757813  79.49864 1 1 1 1
3 10  594.6710815429688  2.669023 1 1 0 1
1 10  709.3441162109375  54.03467 1 1 1 1
3 10  678.5824584960938       100 1 1 1 1
3 10     709.1865234375       100 1 1 0 1
1 10  709.3440551757813       100 1 1 1 1
2 10  280.3486022949219  .1801425 1 1 1 1
end