Creating Table Question

[Davia Downey]

I have cleaned my data (Census of Governments in the United States) and want to create a table with the total amount by category as well as the percentage of each grouping in a table. When I use the command table GovTypeName2 Year, stat (count Total) it isn't showing the total for some reach (only the count of the label I think?). Here's a data snapshot:

* Example generated by -dataex-. For more info, type help dataex
clear
input long(GovType GovTypeName GovTypeName2 Total Year)
1 1 1 38 10
1 1 1 33 15
1 1 1 39  6
1 1 1 33 16
1 1 1 34 13
1 1 1 38 11
1 1 1 35 12
1 1 1 37  7
1 1 1 33 14
1 1 1 36  8
1 1 1 37  9
2 1 2 54 12
2 1 2 52 14
2 1 2 47  6
2 1 2 51  8
2 1 2 49 16
2 1 2 55 10
2 1 2 48  7
2 1 2 53  9
2 1 2 57 13
2 1 2 50 15
2 1 2 56 11
3 1 3 29 16
3 1 3 24  8
3 1 3 25  9
3 1 3 30 13
3 1 3 26 10
3 1 3 31 15
3 1 3 27 11
3 1 3 28 12
3 1 3 22  7
3 1 3 21  6
3 1 3 32 14
4 1 4  5 12
4 1 4  1 16
4 1 4 12  7
4 1 4 13  6
4 1 4  8 10
4 1 4  3 15
4 1 4 10  8
4 1 4  4 14
4 1 4  9  9
4 1 4  7 11
4 1 4  6 13
end
label values GovType GovType
label def GovType 1 "1", modify
label def GovType 2 "2", modify
label def GovType 3 "3", modify
label def GovType 4 "4", modify
label values GovTypeName GovTypeName
label def GovTypeName 1 "Government Organization", modify
label values GovTypeName2 GovTypeName2
label def GovTypeName2 1 "Total Local Government Units - County Governments", modify
label def GovTypeName2 2 "Total Local Government Units - Subcounty Governments", modify
label def GovTypeName2 3 "Total Local Government Units - Subcounty Governments - Municipal Governments", modify
label def GovTypeName2 4 "Total Local Government Units - Subcounty Governments - Township Governments", modify
label values Total Total
label def Total 1 "16214", modify
label def Total 3 "16253", modify
label def Total 4 "16360", modify
label def Total 5 "16504", modify
label def Total 6 "16519", modify
label def Total 7 "16629", modify
label def Total 8 "16656", modify
label def Total 9 "16691", modify
label def Total 10 "16734", modify
label def Total 12 "16822", modify
label def Total 13 "16991", modify
label def Total 21 "18517", modify
label def Total 22 "18862", modify
label def Total 24 "19076", modify
label def Total 25 "19200", modify
label def Total 26 "19279", modify
label def Total 27 "19372", modify
label def Total 28 "19429", modify
label def Total 29 "19491", modify
label def Total 30 "19492", modify
label def Total 31 "19495", modify
label def Total 32 "19519", modify
label def Total 33 "3031", modify
label def Total 34 "3033", modify
label def Total 35 "3034", modify
label def Total 36 "3041", modify
label def Total 37 "3042", modify
label def Total 38 "3043", modify
label def Total 39 "3044", modify
label def Total 47 "35508", modify
label def Total 48 "35684", modify
label def Total 49 "35705", modify
label def Total 50 "35748", modify
label def Total 51 "35810", modify
label def Total 52 "35879", modify
label def Total 53 "35891", modify
label def Total 54 "35933", modify
label def Total 55 "35935", modify
label def Total 56 "36001", modify
label def Total 57 "36011", modify
label values Year Year2
label def Year2 6 "1972", modify
label def Year2 7 "1977", modify
label def Year2 8 "1982", modify
label def Year2 9 "1987", modify
label def Year2 10 "1992", modify
label def Year2 11 "1997", modify
label def Year2 12 "2002", modify
label def Year2 13 "2007", modify
label def Year2 14 "2012", modify
label def Year2 15 "2017", modify
label def Year2 16 "2022", modify

[Clyde Schechter]

Well, before we get to your -table- command, there is a huge problem in your data. You have the variable Total as a value-labeled variable. This is entirely unsuitable for this kind of data. It probably resulted from the source data having the real numbers (16214, 16253, etc.) imported to Stata as a string variable and then somebody applying -encode- to make it numeric. The correct way to get from string variables that actually represent numbers to a numeric variable with the correct values is -destring-, with appropriate options applied depending on how the string variable looked. With -encode-, the values that Stata will calculate with are 1, 2, 3, ..., 57: you can see those in the -label def Total- commands in your -dataex- output. So the data has to be fixed before you can proceed. (You need to fix this not just for this table but before you do anything at all with the Total variable.)

Next, understand that in -table- (and in any native Stata command that creates aggregated statistics, such as -collapse-) -count- does not sum up values. -count- gives you the number of non-missing values of the variable. So your -table- command will just give you the number of observations for each GovTypeName2 and Year combination that have a non-missing value of the variable Total. In your example data, there is exactly one observation for each combination of GovTypeName2 and Year, and the variable Total is never missing, so you get a table with 1 in every cell. I imagine that what you actually want is the value of the (fixed) Total variable in the cells instead. Since there is only one value for each cell, you can actually use any of several operators instead of -count-: mean min max total first last will all work here. On the assumption that in your real data there can be more than one observation for each combination of GovTypeName2 and Year and you want Stata to add up the values of Total in those observations, then it would look like this:

//  FIX TOTAL VARIABLE
decode Total, gen(total)
destring total, replace
drop Total
rename total Total

//  MAKE A TABLE
table GovTypeName2 Year, stat (total Total) stat(percent Total, across(GovTypeName2))

By the way, given that -total- is a Stata keyword, especially in the -table- command, it is probably not a good idea to use Total as a variable name: it gets confusing in both the code and the output as one is never quite sure what one is talking about.

[Davia Downey]

Yes, this is precisely what happened (encode, not destring, was used). That was my cleanup mistake! After decoding, destringing, and renaming the Total variable name, I am (finally) all set. Thank you for pointing out the initial data problem. Much appreciated.