Foreach loop problems

[Anne Dam]

Dear statalist forum,

I am here because I am trying to conduct one task over several excel files located inside one folder and save the resulting data as new excel files in another folder .
But I keep receiving an error "Sensitivity not found" (or sometimes one of the other variables that I am using in the calculation), and I don't understand what has happened.

If anyone can shed light on my issue, I am truly grateful!

Imagine you have several different excel files inside folder "test" with context similar to:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte id float Sensitivity int totalincludedparticipants
1 83  35
2 71  43
3 50 200
end

The code I am trying to run, to open each *.xlsx file inside the folder "test" (global TEST "Path/test") is:

cd $TEST
set trace on
global filenames : dir . files "*.xlsx"
foreach filename in $filenames {

    di "`filename'"

    *Calculate our variables: 

    * 1)Sensitivity with 95% CI
    *first we convert Sensitivity back to fraction
    gen sens=Sensitivity/100
    gen SE_sens=sqrt(sens-(1-sens))/totalincludedparticipants //Im unsure if the SQRT should imnclude totalincludedparticipants or not?
    gen sens_up= sens + 1.96*SE_sens
    gen sens_low= sens - 1.96*SE_sens
    drop SE_sens


    *To save everything inside datatable as a new excelFile:
    export excel using "$EXPORT/new_`filename'", firstrow(variables) replace
    
    clear

}

Output:

- foreach filename in $filenames { = foreach filename in "Book1.xlsx" "Book2.xlsx" { - di "`filename'" = di "Book1.xlsx" Book1.xlsx - gen sens=Sensitivity/100 Sensitivity not found gen SE_sens=sqrt(sens-(1-sens))/totalincludedparticipants gen sens_up= sens + 1.96*SE_sens gen sens_low= sens - 1.96*SE_sens drop SE_sens drop sens export excel using "$EXPORT/`filename'.xlsx", firstrow(variables) replace } r(111);

If you have any idea at all I would greatly appreciate it!!!

Thanks a lot.

[Mike Murphy]

You need to actually import the files into Stata before you can generate the variable

sens

, check out the help file for

import excel

[Clyde Schechter]

Well, I have never known Stata to be wrong when it says a variable is not found. In this case, I think the source of the problem is plain: there is no command inside the loop to read in the files. Consequently when you reach that -gen sens = Sensitivity/100-, Stata is working with an empty data set--there are no variables at all. You need to put an -import excel using `filename'- command at the top of the loop.

Also, to respond to the implied question in your comment, the number of participants should, indeed, be inside the -sqrt()-.

Added: Crossed with #2 which recommends the same diagnosis and treatment.

[Anne Dam]

Dear both,

Thank you so much!! I have tried for hours and I am slightly embarrassed about how simple it really was - everything works now!

Thank you both - I would still be struggling if it was not for you!

Hehe and thank you Clyde, I forgot to delete the comment meant for my co-worker before posting here.

Final code:

cd $TEST
global filenames : dir . files "*.xlsx"
foreach filename in $filenames {

    di "`filename'"
    import excel using `filename', firstrow clear

    *Calculate our variables: 

    * 1)Sensitivity with 95% CI
    *first we convert Sensitivity back to fraction
    gen sens=Sensitivity/100
    gen SE_sens=sqrt((sens-(1-sens))/totalincludedparticipants )
    gen sens_up= sens + 1.96*SE_sens
    gen sens_low= sens - 1.96*SE_sens
    drop SE_sens


    *To save everything inside datatable as a new excelFile:
    export excel using "$EXPORT/new_`filename'", firstrow(variables) replace