Hello,

I am writing a .do file to create 20 tables for each U.S. state using data on state agencies. I provide code below for Table 1. I have made each table shell and saved them as separate Excel files (e.g., Table1; Table2; Table3; etc.). I saved a copy of each table shell in each state folder (e.g., AK; CA; TX; etc.). In sum, I have one folder (called, "states") containing 50 folders named with state abbreviations, and each state folder has the same 20 table shells that are Excel files.

What I am trying to do is use putexcel while looping over `i' values of the state variable in the datafile and `f' folders in the states folder. Right now, the loop is creating Table1 for every state but is saving them all over each other in a newly generated file called "Table1" that is being saved in the "states" folder. In other words, I am ending up with the values for Table1 for Wyoming (WY) that is being saved in a new workbook and not my shells.

I provide the code for one of my tables (table1) below. I think I am pretty close, but am definitely missing something. I've tried a few different things with no luck. Thank you for your time and assistance!

use "C:\....."

set more off
set matsize 11000

cd "C:\.....states"

levelsof state, local(levels)
local folderpath = "C:\.......states"

foreach folder in local folderpath {
foreach i of local levels {

tab b10_i [iw= wgt_r] if state=="`i'"
matrix table1N = r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==1
matrix table1a= r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==2
matrix table1b= r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==3
matrix table1c= r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==4
matrix table1d= r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==5
matrix table1e= r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==6
matrix table1f= r(N)
tab b10_i [iw= wgt_r] if state=="`i'" & b10_i==7
matrix table1g= r(N)

matrix table1n = [(table1N) \ (table1a) \.\(table1b) \(table1c)\ .\(table1d) \(table1e)\.\(table1f)\(table1g)]
mat li table1n

mata: st_matrix("table1ab", st_matrix("table1a") :/ st_matrix("table1N"))
mata: st_matrix("table1bb", st_matrix("table1b") :/ st_matrix("table1N"))
mata: st_matrix("table1cb", st_matrix("table1c") :/ st_matrix("table1N"))
mata: st_matrix("table1db", st_matrix("table1d") :/ st_matrix("table1N"))
mata: st_matrix("table1eb", st_matrix("table1e") :/ st_matrix("table1N"))
mata: st_matrix("table1fb", st_matrix("table1f") :/ st_matrix("table1N"))
mata: st_matrix("table1gb", st_matrix("table1g") :/ st_matrix("table1N"))

matrix table1per = [100.0000 \ (table1ab)\ . \ (table1bb) \ (table1cb)\ .\(table1db) \(table1eb)\.\(table1fb)\(table1gb)]
mat li table1per

putexcel set "Table1", sheet("Table 1") modify
putexcel B12 = matrix(table1n)
putexcel C12 = matrix(table1per)
}
}