stata: sheet name not exist when saving-CodePudding

I'm writing a stata program accessing multiple sheets in an excel file, after my operation on them, I try to save the result, but it shows that the sheet name does not exist (Cabo not found, r(111)) my code is like this:

clear
foreach sheet in "Cabo" "Ga" "Inha" {
import excel using "filename.xlsx", sheet("`sheet'") firstrow
//here are operations//
if `sheet' == "Cabo" {
    save test1018
    }
else {
    append using test1018.dta
    save test1018
    }
}

Does anyone know what I did wrong? Thank you!!

CodePudding user response：

You will also need to include a , replace for when you save the data after the append. You can also simplify the code by starting by saving an empty data set before the loop. Then each sheet will be appended to this dataset and you do not have to treat the first sheet differently to the following sheets.

*Save an empty dataset to append to
clear
save test1018.dta, replace emptyok

foreach sheet in "Cabo" "Ga" "Inha" {
    import excel using "filename.xlsx", sheet("`sheet'") firstrow
    //here are operations//
    append using test1018.dta
    save test1018.dta, replace
}

CodePudding user response：

@TheIceBear has pointed to good technique here.

The point of this answer is just that for three sheets a loop is not essential. This should work too, subject to details about files we cannot possibly check.

import excel using "filename.xlsx", sheet("Cabo") firstrow clear 
save test1018

import excel using "filename.xlsx", sheet("Ga") firstrow clear 
append using test1018
save test1018, replace 

import excel using "filename.xlsx", sheet("Inha") firstrow clear 
append using test1018 
save test1018, replace

I do agree that this is about as far as I would go without writing a loop, but the code is clear, transparent and likely to take you less time to write.