Long data into table using putexcel, put excel error

[Karyn Havas]

Hi all,

First time post. I am learning loops. My data is in columns: BACTERIA, ANTIBIOTIC, RESISTANCE. Antibiotics repeat for each bacteria type. I would like to it into a table in excel where the columns are bacteria types, the rows are antibiotics, and the cells are the resistance data. I am stuck on the getting the resistance data where I need it to go. It seems to go through the loop but the error is in the putexcel command and I cannot figure out why. A lot of the threads in the forum got me to this point, but now I am stuck. I know I can reshape the dataset to a wide format, but I am trying to learn loops and this is a good opportunity.

Thanks for the help!

putexcel set AMRVetTable2022, replace
// place pathogens along the columns
local ncol = 2
levelsof standardbact_condensed, local(path)
foreach i in `path' {
local col: word `ncol' of `c(ALPHA)'
putexcel `col'2 = ("`i'")
local ++ncol
}
//place antibiotics down the rows
local nrow = 3
levelsof standardabx, local(abx)
foreach j in `abx' {
putexcel A`nrow' = ("`j'")
local ++nrow
}

//place % resistance (pathdum) in proper cell in table
encode standardabx, gen(abx)
tab abx, nolabel
encode standardbact_condensed, gen(pathogen)
describe pathogen
tab pathogen, nolabel

putexcel set AMRVetTable2022, modify
set trace on
local N = _N
local nrow = 2
local ncol = 2
forvalues i = 1/`N' { // for each observation
forvalues j=1/18 { // for each pathogen
if pathogen==`j'{
local col: word `ncol' of `c(ALPHA)'
forvalues a = 1/39 { // for each antibiotic
if abx ==`a' {
local row =`nrow'+`a' // start at cell B3
putexcel `col'`row' == pathdum3
}
local ++ncol
}

}
}
}
set trace off

The trace information at "begin putexcel":
- version 13
- if ("`c(excelsupport)'" != "1") {
= if ("1" != "1") {
dis as err "putexcel is not supported on this platform."
exit 198
}
- if (_caller()<14.1) {
cap syntax anything(name=cellexplist id="cellexplist" equalok) using/ [, SHeet(string) COLWise MODify KEEPC
> ELLFormat locale(string) REPLACE]
if _rc {
if ("`1'" == "set") {
gettoken subcmd 0 : 0, parse(" ,")
local 0 `"using `0'"'
syntax using / [,MODify REPLACE SHeet(string) KEEPCELLFormat locale(string)]
mata : putexcel_adv_set()
}
else if ("`1'" == "describe") {
if ("`2'" != "") {
di as err "invalid syntax"
exit 198
}
mata : putexcel_adv_describe()
}
else if ("`1'" == "clear") {
if ("`2'" != "") {
di as err "invalid syntax"
exit 198
}
mata : putexcel_adv_clear()
}
else {
syntax anything(name=cellexplist id="cellexplist" equalok) [, SHeet(string) COLWise]
mata : putexcel_adv_cellexplist()
}
}
else {
mata : putexcel()
}
}
- else {
- if ("`1'" == "set") {
= if ("B4" == "set") {
gettoken subcmd 0 : 0, parse(" ,")
local 0 `"using `0'"'
syntax using / [, REPLACE MODify SHeet(string asis) locale(string) OPEN]
mata : putexcel_set_new()
}
- else if ("`1'" == "describe") {
= else if ("B4" == "describe") {
if ("`2'" != "") {
di as err "invalid syntax"
exit 198
}
mata : putexcel_describe_new()
}
- else if ("`1'" == "clear") {
= else if ("B4" == "clear") {
if ("`2'" != "") {
di as err "invalid syntax"
exit 198
}
mata : putexcel_clear_new()
}
- else if ("`1'" == "close") {
= else if ("B4" == "close") {
if ("`2'" != "") {
di as err "invalid syntax"
exit 198
}
mata : putexcel_close_new()
}
- else if ("`1'" == "save") {
= else if ("B4" == "save") {
if ("`2'" != "") {
di as err "invalid syntax"
exit 198
}
mata : putexcel_close_new()
}
- else {
- syntax anything(name=cellexplist id="cellexplist" equalok) [, OVERWRitefmt asdate asdatetime asdatenum asda
> tetimenum names rownames colnames COLWise NFORmat(string) script(string) nobold noitalic noUNDERLine noSTRIKE
> out notxtwrap noSHRINKfit noFMTLock noFMTHidden hcenter left right bottom top vcenter TXTINdent(integer -1) T
> XTROtate(integer -1) font(string asis) BORDer(string asis) DBORDer(string asis) FPATtern(string asis) merge u
> nmerge ]
- mata : putexcel_cellexplist_new()
invalid syntax
}
}
-------------------------------------------------------------------------------------------- end putexcel ---
}
local ++ncol
}
}
}
}
r(198);

[Clyde Schechter]

I think you are trying to turn a screw with a hammer. You do not show example data, so I cannot write code, but the overall approach I would take here is to use -reshape wide- to organize the data the way you want it, and then use -export excel- to copy it all into a spreadsheet.

[Karyn Havas]

Thanks Clyde, I knew about the reshape, it is the easier process. I appreciate the feedback and will show example data in the future.