Tabulate values into 4 quarters in a page

[Chris Callegari]

#15569

Hi All,

I have posted this question in the STATAlist site linked here. They recommended I use asdocx, so I wanted to pose the same question here.

I have a dataset that I want to show the prices before and after a certain date in two separate sections of a tabulate output. My dataset (please see a snippet below) has products in “prod,” prices “rel_px,” dates “ym,” and country “country.” Ideally, I would like a table in a word document which has UK on one side and Ireland on the other divided into top and bottom by pre-tax (prior to mar2016) and post tax (after mar2016). Does anyone know how to create these 4 quadrants all within one table aside from manually copying and pasting the values myself? I would like the table to include the mean and std dev for rel_px and number of observations for each good. I’m not sure if there’s a “style” for the outreg2 function, but it would be great if this table could aesthetically look similar the outreg2 output–if not, I’m sure whatever the output is would look great as well. My code for each of the 4 quadrants and a snippet of my dataset is pasted below. Please let me know if you have any ideas for this. Thank you all so much!

tabulate prod if country=="Ireland" & ym<676, summarize(rel_px) //prior to tax announcement
tabulate prod if country=="Ireland" & ym>=676, summarize(rel_px) //post tax announcement

tabulate prod if country=="UK" & ym<674, summarize(rel_px) //prior to announcement
tabulate prod if country=="UK" & ym>=674, summarize(rel_px) //post announcement

input str21 prod str7 country float ym
"Allitems" "Ireland" 648
"Allitems" "Ireland" 649
"Allitems" "Ireland" 650
"Allitems" "Ireland" 651
"Allitems" "Ireland" 652
"Allitems" "Ireland" 653
"Allitems" "Ireland" 654
"Allitems" "Ireland" 655
"Allitems" "Ireland" 656
"Allitems" "Ireland" 657
"Allitems" "Ireland" 658
"Allitems" "Ireland" 659
"Allitems" "Ireland" 660
"Allitems" "Ireland" 661
"Allitems" "Ireland" 662
"Allitems" "Ireland" 663
"Allitems" "Ireland" 664
"Allitems" "Ireland" 665
"Allitems" "Ireland" 666
"Allitems" "Ireland" 667
"Allitems" "Ireland" 668
"Allitems" "Ireland" 669
"Allitems" "Ireland" 670
"Allitems" "Ireland" 671
"Allitems" "Ireland" 672
"Allitems" "Ireland" 673
"Allitems" "Ireland" 674
"Allitems" "Ireland" 675
"Allitems" "Ireland" 676
"Allitems" "Ireland" 677
"Allitems" "Ireland" 678
"Allitems" "Ireland" 679
"Allitems" "Ireland" 680
"Allitems" "Ireland" 681
"Allitems" "Ireland" 682
"Allitems" "Ireland" 683
"Allitems" "Ireland" 684
"Allitems" "Ireland" 685
"Allitems" "Ireland" 686
"Allitems" "Ireland" 687
"Allitems" "Ireland" 688
"Allitems" "Ireland" 689
"Allitems" "Ireland" 690
"Allitems" "Ireland" 691
"Allitems" "Ireland" 692
"Allitems" "Ireland" 693
"Allitems" "Ireland" 694
"Allitems" "Ireland" 695
"Allitems" "Ireland" 696
"Allitems" "Ireland" 697
"Allitems" "Ireland" 698
"Allitems" "Ireland" 699
"Allitems" "Ireland" 700
"Allitems" "Ireland" 701
"Allitems" "Ireland" 702
"Allitems" "Ireland" 703
"Allitems" "Ireland" 704
"Allitems" "Ireland" 705
"Allitems" "Ireland" 706
"Allitems" "Ireland" 707
"Allitems" "Ireland" 708
"Allitems" "Ireland" 709
"Allitems" "Ireland" 710
"Allitems" "Ireland" 711
"Allitems" "Ireland" 712
"Allitems" "Ireland" 713
"Allitems" "Ireland" 714
"Allitems" "Ireland" 715
"Allitems" "Ireland" 716
"Allitems" "Ireland" 717
"Allitems" "Ireland" 718
"Allitems" "Ireland" 719
"Allitems" "Ireland" 720
"Allitems" "Ireland" 721
"Allitems" "Ireland" 722
"Allitems" "Ireland" 723
"Allitems" "Ireland" 724
"Allitems" "Ireland" 725
"Allitems" "Ireland" 726
"Allitems" "Ireland" 727
"Allitems" "Ireland" 728
"Bread"    "Ireland" 648
"Bread"    "Ireland" 649
"Bread"    "Ireland" 650
"Bread"    "Ireland" 651
"Bread"    "Ireland" 652
"Bread"    "Ireland" 653
"Bread"    "Ireland" 654
"Bread"    "Ireland" 655
"Bread"    "Ireland" 656
"Bread"    "Ireland" 657
"Bread"    "Ireland" 658
"Bread"    "Ireland" 659
"Bread"    "Ireland" 660
"Bread"    "Ireland" 661
"Bread"    "Ireland" 662
"Bread"    "Ireland" 663
"Bread"    "Ireland" 664
"Bread"    "Ireland" 665
"Bread"    "Ireland" 666
end
format %tm ym

[Dr. Attaullah Shah]

#15578

For this type of tables, the flexmat program is ideal. If you need such tables on frequent basis, you can create a custom template and use it conveniently. Below I show how to make this table piece by piece , using various functionalities of flexmat. Please do update asdocx before using the following solution.

 * To update asdocx
asdocx_update

clear
input str21 prod str7 Country float(ym rel_px)
"Allitems" "Ireland" 648  34.88717
"Allitems" "Ireland" 649  26.68857
"Allitems" "Ireland" 650  13.66463
"Allitems" "Ireland" 651  2.855687
"Allitems" "Ireland" 652  86.89333
"Allitems" "Ireland" 653  35.08549
"Allitems" "Ireland" 654  7.110509
"Allitems" "Ireland" 655 32.336796
"Allitems" "Ireland" 656  55.51031
"Allitems" "Ireland" 657   87.5991
"Allitems" "Ireland" 658  20.47095
"Allitems" "Ireland" 659  89.27587
"Allitems" "Ireland" 660  58.44658
"Allitems" "Ireland" 661 36.977913
"Allitems" "Ireland" 662  85.06309
"Allitems" "Ireland" 663  39.13819
"Allitems" "Ireland" 664  11.96613
"Allitems" "Ireland" 665  75.42434
"Allitems" "Ireland" 666 69.502335
"Allitems" "Ireland" 667  68.66152
"Allitems" "Ireland" 668  93.19346
"Allitems" "Ireland" 669  45.48882
"Allitems" "Ireland" 670   6.74011
"Allitems" "Ireland" 671  33.79889
"Allitems" "Ireland" 672  97.48848
"Allitems" "Ireland" 673  72.64384
"Allitems" "Ireland" 674 4.5415115
"Allitems" "Ireland" 675 74.596664
"Allitems" "Ireland" 676  49.61259
"Allitems" "Ireland" 677 71.671616
"Allitems" "Ireland" 678  85.97421
"Allitems" "Ireland" 679 13.407556
"Allitems" "Ireland" 680  48.84418
"Allitems" "Ireland" 681  87.12188
"Allitems" "Ireland" 682  76.64684
"Allitems" "Ireland" 683 25.125553
"Allitems" "Ireland" 684 16.636478
"Allitems" "Ireland" 685 74.379585
"Allitems" "Ireland" 686  98.05113
"Allitems" "Ireland" 687  72.95772
"Allitems" "Ireland" 688  90.11049
"Allitems" "Ireland" 689 26.436493
"Allitems" "Ireland" 690  88.56509
"Allitems" "Ireland" 691   88.2112
"Allitems" "Ireland" 692 74.893295
"Allitems" "Ireland" 693  91.96262
"Allitems" "Ireland" 694  69.34533
"Allitems" "Ireland" 695  21.54026
"Allitems" "Ireland" 696  82.85889
"Allitems" "Ireland" 697 4.4215355
"Allitems" "Ireland" 698  86.30377
"Allitems" "Ireland" 699 35.260456
"Allitems" "Ireland" 700  77.20399
"Allitems" "Ireland" 701  58.61199
"Allitems" "Ireland" 702 32.277664
"Allitems" "Ireland" 703 17.293066
"Allitems" "Ireland" 704  80.53645
"Allitems" "Ireland" 705  30.60019
"Allitems" "Ireland" 706  21.90997
"Allitems" "Ireland" 707   72.4731
"Allitems" "Ireland" 708  69.64866
"Allitems" "Ireland" 709  91.19344
"Allitems" "Ireland" 710 67.956345
"Allitems" "Ireland" 711  35.49416
"Allitems" "Ireland" 712    73.897
"Allitems" "Ireland" 713 18.740166
"Allitems" "Ireland" 714  31.46128
"Allitems" "Ireland" 715  13.75693
"Allitems" "Ireland" 716  65.37739
"Allitems" "Ireland" 717 27.013193
"Allitems" "Ireland" 718  89.98394
"Allitems" "Ireland" 719  57.34232
"Allitems" "Ireland" 720 11.147037
"Allitems" "Ireland" 721  41.45227
"Allitems" "Ireland" 722  .3052204
"Allitems" "Ireland" 723  66.59978
"Allitems" "Ireland" 724 34.628757
"Allitems" "Ireland" 725  7.802349
"Allitems" "Ireland" 726 12.758135
"Allitems" "Ireland" 727  22.97006
"Allitems" "Ireland" 728 32.955467
"Bread"    "Ireland" 648  41.44089
"Bread"    "Ireland" 649  3.608474
"Bread"    "Ireland" 650  8.438108
"Bread"    "Ireland" 651  .9876246
"Bread"    "Ireland" 652  32.00437
"Bread"    "Ireland" 653 .51969653
"Bread"    "Ireland" 654 22.754347
"Bread"    "Ireland" 655   85.1468
"Bread"    "Ireland" 656  98.20066
"Bread"    "Ireland" 657 3.2479186
"Bread"    "Ireland" 658  98.74847
"Bread"    "Ireland" 659  89.41061
"Bread"    "Ireland" 660  96.84734
"Bread"    "Ireland" 661  23.92203
"Bread"    "Ireland" 662  69.27336
"Bread"    "Ireland" 663  48.84359
"Bread"    "Ireland" 664  43.76452
"Bread"    "Ireland" 665  58.58005
"Bread"    "Ireland" 666 37.870922
"Bread"    "Ireland" 666 37.870922
"Allitems" "Ireland" 648  68.80603
"Allitems" "England" 649  97.94578
"Allitems" "England" 650  67.01937
"Allitems" "England" 651  59.48808
"Allitems" "England" 652  79.70892
"Allitems" "England" 653  78.35854
"Allitems" "England" 654  65.46342
"Allitems" "England" 655  9.688908
"Allitems" "England" 656 68.850586
"Allitems" "England" 657   87.2496
"Allitems" "England" 658  52.96353
"Allitems" "England" 659  83.02209
"Allitems" "England" 660  93.39853
"Allitems" "England" 661 17.498913
"Allitems" "England" 662  55.36171
"Allitems" "England" 663  53.46152
"Allitems" "England" 664  77.67793
"Allitems" "England" 665  12.88747
"Allitems" "England" 666  27.75184
"Allitems" "England" 667  42.42016
"Allitems" "England" 668 13.590055
"Allitems" "England" 669  33.25624
"Allitems" "England" 670  46.75523
"Allitems" "England" 671  51.60881
"Allitems" "England" 672  6.694305
"Allitems" "England" 673  7.229638
"Allitems" "England" 674  68.17465
"Allitems" "England" 675  8.804953
"Allitems" "England" 676 13.270818
"Allitems" "England" 677  87.45816
"Allitems" "England" 678  24.68877
"Allitems" "England" 679 4.3254995
"Allitems" "England" 680 37.644367
"Allitems" "England" 681  76.77862
"Allitems" "England" 682 75.513664
"Allitems" "England" 683  44.76189
"Allitems" "England" 684  40.87105
"Allitems" "England" 685 29.777426
"Allitems" "England" 686  67.94177
"Allitems" "England" 687  71.24024
"Allitems" "England" 688  56.62265
"Allitems" "England" 689 17.783249
"Allitems" "England" 690 11.399897
"Allitems" "England" 691  59.55869
"Allitems" "England" 692  62.51604
"Allitems" "England" 693  63.48989
"Allitems" "England" 694  99.44572
"Allitems" "England" 695 74.976776
"Allitems" "England" 696 17.367882
"Allitems" "England" 697  61.07705
"Allitems" "England" 698  57.54215
"Allitems" "England" 699 36.781612
"Allitems" "England" 700 30.052456
"Allitems" "England" 701  .7538024
"Allitems" "England" 702  67.01369
"Allitems" "England" 703  42.41406
"Allitems" "England" 704  95.37622
"Allitems" "England" 705  8.674778
"Allitems" "England" 706  89.49648
"Allitems" "England" 707  58.90286
"Allitems" "England" 708  40.05832
"Allitems" "England" 709  66.54901
"Allitems" "England" 710  41.98386
"Allitems" "England" 711 74.720535
"Allitems" "England" 712 71.901436
"Allitems" "England" 713  84.64647
"Allitems" "England" 714  79.08313
"Allitems" "England" 715 19.002222
"Allitems" "England" 716  38.69604
"Allitems" "England" 717 23.871336
"Allitems" "England" 718 34.470016
"Allitems" "England" 719  77.95682
"Allitems" "England" 720 74.843956
"Allitems" "England" 721 23.037836
"Allitems" "England" 722 16.770323
"Allitems" "England" 723  91.80508
"Allitems" "England" 724  31.38996
"Allitems" "England" 725  90.19141
"Allitems" "England" 726  7.740517
"Allitems" "England" 727  63.41382
"Allitems" "England" 728  81.47295
"Bread"    "England" 648  87.88922
"Bread"    "England" 649  2.599352
"Bread"    "England" 650 17.993002
"Bread"    "England" 651  57.78895
"Bread"    "England" 652  40.81415
"Bread"    "England" 653  61.55495
"Bread"    "England" 654 17.457695
"Bread"    "England" 655 36.176464
"Bread"    "England" 656  13.38996
"Bread"    "England" 657  .1363096
"Bread"    "England" 658 25.710005
"Bread"    "England" 659  65.17417
"Bread"    "England" 660  92.52081
"Bread"    "England" 661  82.33367
"Bread"    "England" 662  92.29402
"Bread"    "England" 663  74.80426
"Bread"    "England" 664  52.14148
"Bread"    "England" 665  40.22151
end
format %tm ym

And now the code to generate the table

* Make first part of the table
asdocx tabulate prod if Country=="Ireland" & ym<676, summarize(rel_px) replace

* Run the command again for sub sample of England
asdocx tabulate prod if Country=="England" & ym<676, summarize(rel_px)

* Append this second table to the right of the first, deleting the row names
flexmat addmat, matname(output)  right norownames 

* Delect the second table
flexmat droploc, loc(2)

* Make the third part of the table
asdocx tabulate prod if Country=="Ireland" & ym>=676, summarize(rel_px) 

* Append this new table below the main table 
flexmat addmat, matname(output)  below 
flexmat droploc, loc(2)


asdocx tabulate prod if Country=="England" & ym>=676, summarize(rel_px)
flexmat addmat, matname(output)  row(4) col(5) norownames 
flexmat droploc, loc(2)

* Add super row
flexmat addrow, data(\hmerge_7 Post-Tax) above

* Add sub row
flexmat insertrow, data(\hmerge_3 England) row(2)
flexmat addrow, data(\hmerge_4 Ireland) row(2) col(5)

* Panel seperator
flexmat insertrow, data(\hmerge_7 Post-Tax) row(6)

asdocx exportflex

[Author]

Posts