Three way table help

[Fahad Mirza]

Hello Everyone,

I am looking for some help in making and exporting a three-way table however, I am unable to successfully come across stata command and techniques that could help me with it.
The image of the table is as below:

I tried using table however that does not provide me with totals for each school level category along with grand total by districts. The table also has column totals.

Will be really grateful if anyone can explain the process as I was thinking maybe the use of levelsof, estpost and esttab might help? Thanks.

The data for this is:

* Example generated by -dataex-. To install: ssc install dataex
clear
input long(emiscode dist sch_level sch_gender) float total_school
31110001 2 1 2   18
31110002 2 1 2   18
31110003 2 2 2  194
31110004 2 2 2  194
31110005 2 2 2  194
31110006 2 2 2  194
31110007 2 2 2  194
31110008 2 2 2  194
31110009 2 2 2  194
31110010 2 2 2  194
31110011 2 2 2  194
31110012 2 2 2  194
31110013 2 2 2  194
31110014 2 2 2  194
31110015 2 2 2  194
31110017 2 2 2  194
31110018 2 2 2  194
31110019 2 2 2  194
31110020 2 2 2  194
31110021 2 2 2  194
31110022 2 2 2  194
31110023 2 2 2  194
31110024 2 2 2  194
31110025 2 2 2  194
31110026 2 2 2  194
31110027 2 2 2  194
31110028 2 2 2  194
31110029 2 2 2  194
31110030 2 2 2  194
31110031 2 1 1   18
31110032 2 2 1  194
31110033 2 1 1   18
31110034 2 2 1  194
31110035 2 2 1  194
31110036 2 2 1  194
31110037 2 2 1  194
31110038 2 2 1  194
31110039 2 2 1  194
31110040 2 3 2  363
31110041 2 2 2  194
31110042 2 3 2  363
31110043 2 3 2  363
31110044 2 3 2  363
31110045 2 2 2  194
31110046 2 3 2  363
31110047 2 3 2  363
31110048 2 3 2  363
31110049 2 3 2  363
31110050 2 3 2  363
31110051 2 3 2  363
31110052 2 3 2  363
31110054 2 3 2  363
31110055 2 3 2  363
31110056 2 3 2  363
31110057 2 3 2  363
31110058 2 3 2  363
31110059 2 3 2  363
31110060 2 3 2  363
31110061 2 2 1  194
31110062 2 3 1  363
31110063 2 2 1  194
31110064 2 2 1  194
31110065 2 3 1  363
31110066 2 3 1  363
31110067 2 3 1  363
31110068 2 2 1  194
31110069 2 3 1  363
31110070 2 3 1  363
31110071 2 3 1  363
31110072 2 3 1  363
31110073 2 4 1 1661
31110074 2 3 1  363
31110075 2 3 1  363
31110076 2 2 1  194
31110078 2 3 1  363
31110079 2 3 1  363
31110080 2 3 1  363
31110081 2 2 1  194
31110082 2 3 1  363
31110083 2 3 1  363
31110084 2 4 1 1661
31110085 2 3 1  363
31110086 2 2 1  194
31110087 2 4 2 1661
31110088 2 4 2 1661
31110090 2 4 2 1661
31110091 2 4 2 1661
31110092 2 4 2 1661
31110093 2 4 2 1661
31110095 2 4 2 1661
31110096 2 4 2 1661
31110097 2 4 2 1661
31110098 2 4 2 1661
31110101 2 4 2 1661
31110102 2 4 2 1661
31110103 2 4 2 1661
31110104 2 4 2 1661
31110105 2 4 2 1661
31110106 2 4 2 1661
31110107 2 4 2 1661
31110108 2 4 2 1661
31110109 2 4 2 1661
31110110 2 4 2 1661
31110111 2 4 2 1661
31110113 2 4 2 1661
31110114 2 4 2 1661
31110115 2 4 2 1661
31110116 2 4 2 1661
31110117 2 4 2 1661
31110118 2 4 2 1661
31110119 2 4 2 1661
31110120 2 4 2 1661
31110121 2 4 2 1661
31110122 2 4 2 1661
31110123 2 4 1 1661
31110124 2 4 2 1661
31110127 2 3 2  363
31110130 2 4 2 1661
31110131 2 4 2 1661
31110132 2 4 2 1661
31110133 2 4 2 1661
31110134 2 4 2 1661
31110135 2 4 2 1661
31110136 2 4 2 1661
31110139 2 4 2 1661
31110140 2 4 2 1661
31110141 2 4 2 1661
31110142 2 4 2 1661
31110143 2 4 2 1661
31110144 2 4 2 1661
31110146 2 4 2 1661
31110147 2 4 2 1661
31110148 2 4 2 1661
31110149 2 4 2 1661
31110150 2 3 2  363
31110152 2 4 2 1661
31110154 2 4 2 1661
31110155 2 4 2 1661
31110156 2 4 2 1661
31110157 2 4 2 1661
31110158 2 4 2 1661
31110160 2 4 2 1661
31110162 2 4 2 1661
31110164 2 4 2 1661
31110166 2 4 2 1661
31110167 2 4 2 1661
31110168 2 4 2 1661
31110169 2 4 2 1661
31110177 2 3 2  363
31110179 2 4 2 1661
31110180 2 4 2 1661
31110181 2 4 2 1661
31110184 2 4 1 1661
31110185 2 4 2 1661
31110187 2 4 2 1661
31110189 2 4 2 1661
31110190 2 4 2 1661
31110191 2 4 2 1661
31110193 2 4 2 1661
31110194 2 4 2 1661
31110195 2 4 2 1661
31110196 2 4 2 1661
31110199 2 4 2 1661
31110201 2 4 2 1661
31110202 2 4 2 1661
31110205 2 4 2 1661
31110206 2 4 2 1661
31110207 2 4 2 1661
31110208 2 4 2 1661
31110209 2 4 2 1661
31110211 2 4 2 1661
31110212 2 4 2 1661
31110213 2 4 2 1661
31110214 2 4 2 1661
31110215 2 4 2 1661
31110216 2 4 2 1661
31110217 2 4 2 1661
31110218 2 3 2  363
31110219 2 4 2 1661
31110221 2 4 2 1661
31110222 2 4 2 1661
31110223 2 4 2 1661
31110226 2 4 2 1661
31110227 2 4 2 1661
31110228 2 4 2 1661
31110232 2 4 2 1661
31110233 2 4 2 1661
31110234 2 4 2 1661
31110235 2 4 2 1661
31110236 2 4 2 1661
31110238 2 4 2 1661
31110239 2 3 2  363
31110240 2 4 2 1661
31110241 2 4 2 1661
31110243 2 4 2 1661
31110244 2 4 2 1661
31110245 2 4 2 1661
31110246 2 4 2 1661
31110249 2 4 2 1661
31110250 2 4 2 1661
31110251 2 4 2 1661
31110252 2 4 2 1661
31110253 2 4 2 1661
31110254 2 4 2 1661
31110256 2 4 2 1661
31110257 2 4 2 1661
31110258 2 4 2 1661
31110259 2 4 2 1661
31110261 2 4 2 1661
31110263 2 4 2 1661
31110266 2 4 2 1661
31110267 2 4 2 1661
31110268 2 4 2 1661
31110269 2 4 2 1661
31110270 2 4 2 1661
31110271 2 3 2  363
31110272 2 4 2 1661
31110274 2 4 2 1661
31110275 2 4 2 1661
31110277 2 4 2 1661
31110278 2 4 2 1661
31110279 2 4 2 1661
31110280 2 4 1 1661
31110281 2 4 1 1661
31110282 2 4 1 1661
31110283 2 4 1 1661
31110284 2 4 1 1661
31110285 2 4 1 1661
31110286 2 4 1 1661
31110287 2 4 1 1661
31110289 2 4 1 1661
31110290 2 4 1 1661
31110291 2 4 1 1661
31110292 2 4 1 1661
31110293 2 4 1 1661
31110294 2 4 1 1661
31110295 2 4 1 1661
31110296 2 4 1 1661
31110297 2 4 1 1661
31110298 2 4 1 1661
31110299 2 4 1 1661
31110300 2 4 1 1661
31110301 2 4 1 1661
31110302 2 3 1  363
31110303 2 4 1 1661
31110304 2 4 1 1661
31110305 2 4 1 1661
31110306 2 4 1 1661
31110307 2 4 1 1661
31110308 2 4 2 1661
31110309 2 4 1 1661
31110311 2 4 1 1661
31110312 2 4 1 1661
31110313 2 4 1 1661
31110315 2 4 1 1661
31110316 2 4 1 1661
31110317 2 4 1 1661
31110318 2 4 1 1661
31110319 2 4 1 1661
31110320 2 4 1 1661
31110321 2 4 1 1661
31110322 2 4 1 1661
31110323 2 4 1 1661
31110324 2 4 1 1661
31110325 2 3 1  363
31110326 2 4 1 1661
31110336 2 4 1 1661
31110339 2 4 1 1661
31110344 2 4 1 1661
31110347 2 4 1 1661
31110348 2 4 1 1661
31110349 2 4 1 1661
31110350 2 4 1 1661
31110352 2 3 1  363
31110353 2 4 1 1661
31110357 2 4 1 1661
31110358 2 4 1 1661
31110359 2 4 1 1661
31110360 2 3 1  363
31110361 2 4 1 1661
31110362 2 4 1 1661
31110363 2 4 1 1661
31110364 2 4 1 1661
31110365 2 4 1 1661
31110366 2 4 1 1661
31110367 2 4 1 1661
31110368 2 4 1 1661
31110370 2 4 1 1661
31110371 2 4 1 1661
31110372 2 4 1 1661
31110373 2 4 1 1661
31110374 2 4 1 1661
31110375 2 4 1 1661
31110376 2 4 1 1661
31110377 2 4 1 1661
31110378 2 4 1 1661
31110379 2 4 1 1661
31110380 2 4 1 1661
31110381 2 4 1 1661
31110382 2 4 1 1661
31110383 2 4 1 1661
31110384 2 4 1 1661
31110385 2 4 1 1661
31110386 2 4 1 1661
31110387 2 4 1 1661
31110389 2 4 1 1661
31110391 2 4 1 1661
31110392 2 4 1 1661
31110395 2 4 1 1661
31110396 2 4 1 1661
31110398 2 4 1 1661
31110400 2 3 1  363
31110401 2 4 1 1661
31110402 2 4 1 1661
31110403 2 4 1 1661
31110405 2 4 1 1661
31110416 2 2 1  194
31110417 2 3 1  363
31110418 2 4 1 1661
31110419 2 4 1 1661
31110420 2 3 1  363
31110432 2 4 1 1661
31110433 2 4 1 1661
31110434 2 4 1 1661
31110437 2 4 1 1661
31110438 2 4 1 1661
31110439 2 4 1 1661
31110440 2 4 1 1661
31110442 2 4 1 1661
31110443 2 3 1  363
31110444 2 3 1  363
31110445 2 4 1 1661
31110446 2 4 1 1661
31110447 2 4 1 1661
31110448 2 4 1 1661
31110449 2 4 1 1661
31110450 2 4 1 1661
31110451 2 4 1 1661
31110452 2 4 1 1661
31110453 2 4 1 1661
31110454 2 4 1 1661
31110455 2 4 1 1661
31110456 2 4 1 1661
31110457 2 4 1 1661
31110459 2 4 1 1661
31110460 2 4 1 1661
31110461 2 4 1 1661
31110462 2 4 1 1661
31110463 2 4 1 1661
31110464 2 3 1  363
31110465 2 4 1 1661
31110466 2 4 1 1661
31110467 2 4 1 1661
31110468 2 4 1 1661
31110469 2 4 1 1661
31110470 2 4 1 1661
31110471 2 4 1 1661
31110472 2 4 1 1661
31110473 2 4 1 1661
31110474 2 4 1 1661
31110476 2 4 1 1661
31110477 2 4 1 1661
31110480 2 4 1 1661
31110481 2 4 1 1661
31110482 2 4 1 1661
31110483 2 4 1 1661
31110484 2 4 1 1661
31110486 2 4 1 1661
31110487 2 4 1 1661
31110488 2 3 1  363
31110490 2 4 1 1661
31110491 2 4 1 1661
31110492 2 4 1 1661
31110493 2 4 1 1661
31110494 2 4 1 1661
31110495 2 4 1 1661
31110496 2 4 1 1661
31110498 2 4 1 1661
31110499 2 4 1 1661
31110501 2 4 1 1661
31110503 2 3 1  363
31110504 2 4 1 1661
31110505 2 4 1 1661
31110506 2 4 1 1661
31110507 2 4 1 1661
31110508 2 4 1 1661
31110510 2 4 1 1661
31110511 2 4 2 1661
31110512 2 4 2 1661
31110513 2 4 2 1661
31110514 2 4 2 1661
31110516 2 5 2   89
31110518 2 4 2 1661
31110520 2 4 2 1661
31110521 2 4 2 1661
31110522 2 4 2 1661
31110523 2 4 2 1661
31110524 2 4 2 1661
31110525 2 4 2 1661
31110526 2 4 2 1661
31110527 2 4 2 1661
31110528 2 4 2 1661
31110529 2 4 2 1661
31110531 2 4 2 1661
31110533 2 4 2 1661
31110536 2 4 2 1661
31110537 2 5 2   89
31110538 2 4 2 1661
31110540 2 3 2  363
31110541 2 4 2 1661
31110546 2 4 2 1661
31110547 2 4 2 1661
31110548 2 4 2 1661
31110549 2 4 2 1661
31110550 2 4 2 1661
31110554 2 4 2 1661
31110559 2 4 2 1661
31110561 2 4 2 1661
31110563 2 4 2 1661
31110569 2 4 2 1661
31110570 2 4 2 1661
31110571 2 4 2 1661
31110572 2 4 2 1661
31110575 2 4 2 1661
31110578 2 4 2 1661
31110579 2 4 2 1661
31110580 2 4 2 1661
31110581 2 4 2 1661
31110583 2 4 2 1661
31110585 2 4 2 1661
31110586 2 4 2 1661
31110587 2 5 2   89
31110620 2 3 2  363
31110621 2 4 2 1661
31110622 2 4 2 1661
31110623 2 4 2 1661
31110624 2 4 2 1661
31110625 2 5 2   89
31110626 2 2 1  194
31110628 2 3 2  363
31110629 2 4 1 1661
31110650 2 3 1  363
31110652 2 4 1 1661
31110653 2 4 1 1661
31110655 2 4 1 1661
31110656 2 4 1 1661
31110658 2 4 1 1661
31110659 2 4 1 1661
31110660 2 4 1 1661
31110661 2 4 1 1661
31110662 2 4 1 1661
31110663 2 4 1 1661
31110664 2 4 2 1661
31110665 2 4 1 1661
31110666 2 4 1 1661
31110667 2 4 1 1661
31110668 2 4 1 1661
31110669 2 4 2 1661
31110670 2 4 2 1661
31110671 2 4 2 1661
31110672 2 4 2 1661
31110673 2 4 2 1661
31110674 2 4 1 1661
31110677 2 4 1 1661
31110680 2 4 2 1661
31120001 2 1 2   18
31120002 2 1 2   18
31120003 2 1 1   18
31120004 2 1 1   18
31120005 2 2 2  194
31120006 2 2 2  194
31120007 2 2 2  194
31120008 2 2 2  194
31120009 2 2 2  194
31120010 2 2 2  194
31120011 2 2 2  194
31120012 2 2 2  194
31120013 2 2 2  194
31120014 2 2 2  194
31120015 2 2 2  194
31120016 2 2 2  194
31120017 2 2 2  194
31120018 2 2 2  194
31120019 2 2 2  194
31120020 2 2 2  194
31120021 2 2 2  194
31120022 2 2 2  194
31120023 2 2 2  194
31120024 2 2 2  194
31120025 2 2 2  194
31120026 2 2 2  194
31120027 2 2 2  194
31120028 2 2 1  194
31120029 2 2 1  194
31120030 2 2 1  194
31120031 2 2 1  194
31120032 2 2 1  194
31120033 2 2 1  194
31120034 2 2 1  194
31120035 2 2 1  194
end
label values dist dist
label def dist 2 "BAHAWALNAGAR", modify
label values sch_level sch_level
label def sch_level 1 "Higher Secondary", modify
label def sch_level 2 "Secondary", modify
label def sch_level 3 "Elementary", modify
label def sch_level 4 "Primary", modify
label def sch_level 5 "Masjid Maktab", modify
label values sch_gender sch_gender
label def sch_gender 1 "Female", modify
label def sch_gender 2 "Male", modify

[Marcos Almeida]

No exactly so, but the example 3 of the - table - command (in the Stata Manual) provides something similar. More than that you’ll find in user-written programs, such as tabout.

[Fahad Mirza]

Hello Marcos,

Thank you for getting back however, i think with tabout you can't really make a three way table as the command is based on the tabulate function. I seen to only get totals and not total by gender in it.

[Fahad Mirza]

Sorry for bumping this post, just needed help in what way i can get such tables

[Justin Niakamal]

If you're trying to export this to a Latex document then tabout (from SSC) might be of interest. I think you can accomplish this using putexcel and some sort of loop. I myself have never used putexcel, so I'm not sure if there's a more efficient way. I've done this using one school, hopefully you can build off of this.

ds emiscode total_school, not
foreach x in `r(varlist)' {
    decode `x', gen(v_`x')
    drop `x'
    ren v_`x' `x'

}

collapse (sum) total_school , by(dist sch_level sch_gender)
reshape wide total_school , i(dist sch_level) j(sch_gender)string

egen total_schoolTotal = rowtotal(total_*)
ren total_school* *
replace sch_level = strtoname(sch_level)

ds sch_level dist, not
reshape wide `r(varlist)', i(dist) j(sch_level)string

order dist
replace dist =  proper(dist)

// For example
keep dist *Elementary*
putexcel set Table1.xlsx, sheet(Sheet1) replace
putexcel B1:D1 = "Elementary",  hcenter merge
putexcel A1 = "District"


putexcel A3 = dist[1]

putexcel B2 = "Male"
putexcel C2 = "Female"
putexcel D2 = "Total"

putexcel B3 = MaleElementary[1]
putexcel C3 = FemaleElementary[1]
putexcel D3 = TotalElementary[1]

[Fahad Mirza]

Thank you Justin for the code I will definitely have a look at it.

However, I was trying to write this loop that created a matrix which looked like the table above and the code for that is:

levelsof dist, local(levels)
matrix define R1 = J(36,4,.)
local k = 1

foreach L of local levels{

matrix R1[`k', 1]= `L'

quietly: tab dist sch_level, matcell(totals)
quietly: tab dist sch_level if sch_gender==1, matcell(totals1)
quietly: tab dist sch_level if sch_gender==2, matcell(totals2)

matrix R1[`k', 2] = totals2[`k', 1]
matrix R1[`k', 3] = totals1[`k', 1]
matrix R1[`k', 4] = totals[`k', 1]

local k = `k' + 1

}

The code works well for the first 4 columns however, I do not know how to replicate this code to work for all columns. If you or anyone else for that matter can help me with this code? I will be very grateful.

Also, if you all can point me to some looping learning materials, that will also be great!

The most basic and probably the most inefficient way of filling up the first 16 columns is listed below:

levelsof dist, local(levels)
matrix define R1 = J(36,16,.)
local k = 1


foreach L of local levels{

matrix R1[`k', 1]= `L'

quietly: tab dist sch_level, matcell(totals)
quietly: tab dist sch_level if sch_gender==1, matcell(totals1)
quietly: tab dist sch_level if sch_gender==2, matcell(totals2)

matrix R1[`k', 2] = totals2[`k', 1]
matrix R1[`k', 3] = totals1[`k', 1]
matrix R1[`k', 4] = totals[`k', 1]

matrix R1[`k', 5] = totals2[`k', 2]
matrix R1[`k', 6] = totals1[`k', 2]
matrix R1[`k', 7] = totals[`k', 2]

matrix R1[`k', 8] = totals2[`k', 3]
matrix R1[`k', 9] = totals1[`k', 3]
matrix R1[`k', 10] = totals[`k', 3]

matrix R1[`k', 11] = totals2[`k', 4]
matrix R1[`k', 12] = totals1[`k', 4]
matrix R1[`k', 13] = totals[`k', 4]

matrix R1[`k', 14] = totals2[`k', 5]
matrix R1[`k', 15] = totals1[`k', 5]
matrix R1[`k', 16] = totals[`k', 5]

local k = `k' + 1

}

Works perfectly fine but as you can see, it is not elegant and definitely not efficient. How can I automate this and reduce steps.