adding multiple tables to an existing sheet in excel or buliding two/three way table?

[Marta Baltruszewicz]

Hello,

I'm trying to build three tables (each one for a country based on var country_id) in excel. AN example of a country looks like this:

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 column and row?
2) is it possible to build a table in stata that looks like the one above? which function should I use?

Any help will be very appreciated as it would save me tons of time in producing these tables in excel. Thank you!

* 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  9  293.0574035644531 13.016968 1 1 0 1
1  6 293.05743408203125  5.097307 1 1 1 1
1  1 293.05743408203125 .13516809 0 1 0 1
1  8 293.05743408203125 10.114495 1 1 0 1
1  9 293.05743408203125 2.1032376 0 1 0 1
1  3 293.05743408203125  2.638437 1 1 0 1
1  3  280.3486022949219       100 1 1 0 1
1  3  280.3486022949219   2.58706 1 1 0 1
1 10  280.3486022949219  .1801425 1 1 1 1
1  6  280.3486022949219  .6729252 1 1 0 1
1  2  280.3486022949219 1.2994117 0 1 0 1
1  8  292.9923400878906  43.59368 0 1 1 1
1  7  292.9923400878906  1.845998 1 1 0 1
1  8  292.9923400878906  2.721675 0 1 0 1
1  4  292.9923400878906  .2703528 0 1 0 1
1  8  292.9923400878906       100 0 1 0 1
1  5  292.9923400878906       100 1 1 1 1
1  4    621.49072265625  .9786306 1 1 0 1
1  3    621.49072265625  .5164462 1 1 0 1
1  1    621.49072265625  .5868586 1 1 0 1
1  4    621.49072265625  .4348797 1 1 1 1
1  4    621.49072265625   .879786 1 1 0 1
1  3    621.49072265625  1.496123 1 1 0 1
1  2    621.49072265625 .14939886 1 1 1 1
1  8    621.49072265625  10.10629 0 1 1 1
1  9    621.49072265625   9.39954 1 1 0 1
1  4    621.49072265625       100 1 1 1 1
1  9    621.49072265625       100 1 1 1 1
1  3    621.49072265625  .8172332 1 1 1 1
1 10  594.6710815429688  2.669023 1 1 0 1
1  7  594.6710205078125  .7204735 1 1 0 1
1  1  594.6710205078125 1.1096567 1 1 0 1
1  6  594.6710815429688  2.948138 1 1 0 1
1  6  594.6710205078125  2.185974 1 1 0 1
1  4  594.6710815429688  8.831465 1 1 1 1
1  5    621.49072265625 .39879015 0 1 0 1
1 10    621.49072265625  2.721675 1 1 0 1
1  5    621.49072265625 1.1613582 1 1 0 1
1  1    621.49072265625  .9239903 1 1 0 1
1 10    621.49072265625  2.723702 1 1 0 1
1  5    621.49072265625 1.4160714 1 1 0 1
1  5     709.1865234375       100 1 1 0 1
1  5  709.1865844726563   44.9127 1 1 0 1
1  7     709.1865234375  35.05869 1 1 1 1
1 10     709.1865234375       100 1 1 0 1
1 10     709.1865234375  46.91713 1 1 0 1
1  4     709.1865234375  5.002633 1 1 0 1
1 10  709.3441162109375  54.03467 1 1 1 1
1  9  709.3440551757813       100 1 1 0 1
1  8  709.3441162109375       100 1 1 1 1
1 10  709.3440551757813  79.49864 1 1 1 1
1  9  709.3440551757813       100 1 1 1 1
1  1  709.3440551757813       100 1 1 0 1
1 10  709.3441162109375  29.13664 1 1 1 1
1 10  709.3440551757813       100 1 1 1 1
1  4     709.1865234375       100 0 1 0 1
1  1     709.1865234375  2.331698 1 1 0 1
1  3     709.1865234375  1.523302 1 1 1 1
1  6     709.1865234375 2.0540721 1 1 0 1
1  7     709.1865234375  1.837703 1 1 0 1
1  8  709.1865844726563  9.268721 1 1 1 1
1 10  678.5824584960938       100 1 1 1 1
1 10  678.5824584960938       100 1 1 0 1
1  7  678.5824584960938       100 1 1 0 1
1  5  678.5824584960938 .50563604 1 1 0 1
1  8  709.1865844726563  3.384126 1 1 0 1
1  6     709.1865234375  2.161576 1 1 0 1
1  6     709.1865234375 3.7826114 1 1 0 1
1  7  709.1865844726563 18.756695 1 1 1 1
1  8     709.1865234375       100 1 1 1 1
1  6     709.1865234375 1.1050651 1 1 0 1
1  5  709.3441162109375   8.61513 1 1 0 1
1  8  709.3441162109375  4.841481 1 1 0 1
1  4  709.3440551757813  .4212497 1 1 0 1
1  6  709.3440551757813  .3847249 1 1 0 1
1  8  709.3441162109375  3.522251 1 0 0 1
1  7  709.3441162109375  .3755092 1 1 0 1
1  6 1357.1649169921875       100 1 1 0 1
1  5 1357.1649169921875 .48106995 1 1 0 1
1  2 1357.1649169921875 2.3599381 1 0 0 1
1  4 1357.1649169921875  2.162031 1 1 0 1
1  6 1357.1649169921875  .7953057 1 1 0 1
1  2 1357.1649169921875  .7133285 1 1 0 1
1  9     709.1865234375 1.4885077 1 1 0 1
1  2     709.1865234375  .6814974 1 1 0 1
1  7     709.1865234375 1.0896412 1 0 0 1
1  7     709.1865234375  .4216659 1 0 0 1
1  6  709.1865844726563  .4804441 1 0 0 1
1  6     709.1865234375 14.915644 1 1 0 1
1  5  709.3440551757813       100 1 1 0 1
1  9  709.3440551757813       100 1 1 1 1
1  8  709.3440551757813  1.288522 1 0 1 1
1  8  709.3440551757813  42.56044 1 1 0 1
1  7  709.3440551757813  20.40264 1 0 0 1
1  9  709.3440551757813  7.307757 1 1 0 1
1  5 1357.1649169921875 18.288853 1 1 0 1
1  1 1357.1649169921875       100 1 1 1 1
1  4 1357.1649169921875       100 1 1 1 1
1  8 1357.1649169921875       100 1 1 0 1
1  8 1357.1649169921875       100 1 1 1 1
end

[Marta Baltruszewicz]

sorry I realized that my dataex produced data only for one country. Here is a mix of all three countries:

* 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