[George Ford]

#11 works

[Daniel McAdams]

Hemanshu Kumar it finally worked! HUGE THANKS to you and Clyde Schechter. I then tried the code suggested by George Ford in #11 which worked (!) but created a separate "countryexport" excel. I want to save each country file inside "${output}/scorecard_data.xlsx" as a new sheet. I ran this but got "file C:\Users\mcdaniela\Documents\2022/stataoutput/scorecard_data.xlsx could not be saved"

forvalues i = 1/31 {
local ctry: word `i' of `countries'
preserve
keep if countrycode == "`ctry'"
*save "${output}/`ctry'_LAC.dta", replace

levelsof countrycode, local(levels)
foreach c of local levels {
export excel using "${output}/scorecard_data.xlsx" if country=="`c'", ///
sheet("`c'") sheetreplace
}

restore
}

[George Ford]

You don't need to preserve/restore with #11 or put it inside an outer loop.

[Clyde Schechter]

"file C:\Users\mcdaniela\Documents\2022/stataoutput/scorecard_data.xlsx could not be saved"

There are several possibilities here. One possibility is that you don't have write access in that directory, or that the drive/directory is full. So use some other application to verify that you can, in fact, save a new file of approximately the same size in that directory. If you can't, then you have identified the problem and the solution will have nothing to do with Stata and everything to do with your IT resources.

The more likely possibility is that you are overwhelming the operating system with all the file reading and writing. File I/O is very slow, and Stata does not wait for those operations to be completed before proceeding to the next command. If there isn't enough computation happening between consecutive I/O operations, the operating system's buffers get filled up and it tells Stata "I can't keep up." That results in the message you are getting.

The easiest way to trim the I/O operations in your case may be* to get rid of the -preserve- and -restore- commands (and the -keep if countrycode == "`ctry'"- command that makes them necessary). There is no need for them because, instead, you can condition the -export excel- command.

forvalues i = 1/31 {
    local ctry: word `i' of `countries'
    *save "${output}/`ctry'_LAC.dta", replace
    levelsof countrycode, local(levels)
    foreach c of local levels {
        export excel ///
            using "${output}/scorecard_data.xlsx" if country=="`ctry'", ///
            sheet("`c'") sheetreplace
    }
}

That may or may not be enough to resolve the problem. Depending on the amount of data associated with each country, the consecutive -export excel- operations may still come up faster than the operating system can handle them. So if the above code still gives you the same message, the next step is to force Stata to slow down.

forvalues i = 1/31 {
    local ctry: word `i' of `countries'
    *save "${output}/`ctry'_LAC.dta", replace
    levelsof countrycode, local(levels)
    foreach c of local levels {
        export excel ///
            using "${output}/scorecard_data.xlsx" if country=="`ctry'", ///
            sheet("`c'") sheetreplace
        sleep 500
    }
}

The -sleep 500- command will cause Stata to pause for a half second (500 milliseconds) after each -export excel- command. That will usually give the operating system enough time to deal with it, so it will be ready for the next one when Stata gets there. You can experiment a bit with the number in the sleep command. It might work with a smaller number, or it might need a larger number. It all depends on how busy the operating system is with other tasks, the write speed of your drive, and, if you are working over a network, the speed of your connections. I have found that 500 usually works on most setups.

*This depends on a bunch of things. If you are running version 16 or 17, Stata may be doing the -preserve- and -restore- operations with frames rather than files on disk. In that case, removing those will not ease the I/O burden on the operating system, and, in fact, the loss of the time spent on those commands (which are somewhat slow even with frames because Stata has to juggle memory) might even exacerbate the problem. Then again, even with version 16 or 17, Stata makes its own decisions about when to use frames and when to use temporary disk files for these operations, so it might help anyway. If you are using version 15 or earlier, definitely -preserve- and -restore- are thrashing the disk and may well be overwhelming the operating system by themselves.

[Daniel McAdams]

Thanks a lot Clyde Schechter --truly appreciate the thorough explanation! I am using version 17. I still cannot save it (I tried all type of range numbers for -sleep- and none worked) so I will have to save each country file as dta (that was my plan B). Big thanks again!