Loop containing putexcel consistently quits, but at inconsistent points

[Samuel Aitken]

I am using putexcel in Stata/SE 14.2 to compile results of a number of cross-tabulations. Essentially, I would like to have one tab produce an overall summary of a categorical frequency and then a number of additional tabs (~35, each corresponding to a subgroup) with the same categorical frequencies. The code seems to run fine and will generate anywhere from 2 to ~20 of the additional tabs before stating "file organism_frequency.xlsx could not be saved". The information that had been placed in the Excel sheet before the program quit is appearing as intended and with the correct values. I'm wondering if this is a technical issue related to saving and if yes, if there is anyway to simplify the code I am using to allow for this to be generated.

The dataset is structured as one string variable ("service") containing the relevant subgroups and 23 categorical variables containing a 1 if present and missing value if absent.

The code I am using is below:

local orgs achromobacter acinetobacter bacillus candida citrobacter ///
	corynebacterium ecoli efaecalis efaecium enterobacter groupbstrep kpneumo ///
	other pseudo proteus rothia saureus sepi smalto smitis spneumo staphspp strepspp afb

putexcel set organism_frequency.xlsx, sheet(Overall) replace
putexcel A1 = "Organism"
putexcel B1 = "Count"

local n : word count `orgs'

forvalues i = 1/`n' {
	local a : word `i' of `orgs'
	local b : variable label `a'
	tab `a'
	local c `r(N)'
	local d = `i'+1
	putexcel A`d' = "`b'"
	putexcel B`d' = `c'
}


levelsof service, local(dept)

foreach service in `dept' {
	putexcel set organism_frequency.xlsx, sheet("`service'") modify
	putexcel A1 = "Organism"
	putexcel B1 = "Count"
	
	local n : word count `orgs'
	
	forvalues i = 1/`n' {
	local a : word `i' of `orgs'
	local b : variable label `a'
	tab `a' if service=="`service'"
	local c `r(N)'
	local d = `i'+1
	putexcel A`d' = "`b'"
	putexcel B`d' = `c'
	}
}

Again, the code works as intended until it is unable to save the Excel file, but at varying numbers of iterations through the loop. Any information is appreciated.

Sam

[Clyde Schechter]

This is just a guess, but I suspect that Stata is filling the operating system's I/O buffers faster than the file system can complete the file open/write/close operations. I have often encountered this problem with -export excel- inside a loop, and particularly when the Excel files are on a network drive (though I have also had it happen, less often, on a local drive). The solution would be to put a -sleep #- command inside the loop just before or just after the -putexcel- commands that write the output, so that Stata will stop and give the OS time to finish its work. The choice of # is more art than science: mostly you figure it out by trial and error. I usually start with 250 and work up or down from there as needed.

[Samuel Aitken]

Thank you for the help! That seemed to be the problem - a sleep command mixed in the loop worked perfectly. This was mapping to a cloud drive which I think made the process even slower.

Out of curiosity, is there any way to store the putexcel commands in local memory to avoid the issue of multiple saved files per loop? That seems as if it would also help this process.

[Clyde Schechter]

I'm not sure what you're referring to when you say local memory. If you mean in RAM, I don't know of any way to do that in Stata. If you mean a file on a local drive, you can just specify such a file in your -putexcel set- command; but if you are doing enough output Stata will still outrun the OS I/O routines and cause the same problem.

[Clyde Schechter]

I just remembered that somebody else on the Forum came up with a neat little program that -capture-s the -putexcel- commands in a loop and keeps retrying them until they execute successfully. This avoids having to figure out how long to make Stata sleep. I can't find that earlier post just now, but you might want to search for it.