Hello,

I provided some code below, but briefly explain my issue first.

I am creating tables in Stata for Excel using the putexcel command. I have a datafile of adult prisons in the 50 US states (1,152 observations total). In this datafile, some states have 20 prisons while other states have 1 prison. The variable I am working with, labeled f50_i, measures prison populations. I am trying to store and then export the sum of a state's prison population to cells in my state-specific Excel tables. In the states with 20 prisons, this would be the sum of all prisoners across the 20 prisons. In the states with 1 prison, it would be the number of prisoners in the one facility in that state.

I could just create a state-specific sum using bysort state: egen popcount = sum(f50_i), but I am trying to do this across multiple tables with different conditioning statements (e.g., if var1!=. & var2==4). This changes the number of prisons (and, therefore, prisoners) in the state across commands. To account for the changing sample of prisons within states across conditioning statements, I used the total command and stored the prison population value using matrix var = [e(b)]. See code below. The problem is that this command is not working when there are zero or one prisons in the state after applying the conditioning statements. When there are zero prisons, I get the error: no observations. When a state has one prison, I get the error: estimates post: matrix has missing values. In both cases, the loop does not work. In the cases with zero prisons (and, therefore, a value of zero on f50_i), I would still like the table to have the value 0 prisoners. Again, my code works fine when there are 2 or more prisons. Could someone suggest one or more alternatives to total that will supply a value in these cases that I can store and export to Excel?

levelsof state, local(levels)
foreach i of local levels {

total f50_i [iw=wgt_long] if state=="`i'"
matrix authorityN = [e(b)]
total f50_i [iw=wgt_long] if state=="`i'" & rc_budget_auth_i==1
matrix authority1a= [e(b)]
total f50_i [iw=wgt_long] if state=="`i'" & rc_personnel_auth_i==1
matrix authority2a= [e(b)]
total f50_i [iw=wgt_long] if state=="`i'" & rc_policies_auth_i==1
matrix authority3a= [e(b)]

matrix authoritytot = [(authorityN) \. \ (authority1a) \ (authority2a) \ (authority3a)]

putexcel set "`i'/Table5b", sheet("Table 5b") modify
putexcel B12 = matrix(authoritytot)
}

Thank you,
Tom S