Looping through a local of strings to call in a file with different sheets

[Rhina Torres]

Hello, I am attempting to to call in different sheets of the same file using a loop with the code below, but I keep getting the error "{ required". Would someone please assist me with diagnosing and correcting the issue? Thank you in advanced!



local coordinator "Erica Frank John Ken Lee Loweye Sam Tyrone"
foreach x of local coordinator "Erica Frank John Ken Lee Loweye Sam Tyrone" {

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-15-23.xlsx", sheet("`x'") firstrow case(lower) clear
keep if assignmentstatus=="Complete"

save "IAP_Assignments_`x'_11-15-23.dta", replace
}

[Clyde Schechter]

The syntax of -foreach...local- is just

foreach x of local coordinator {

You have put in the extraneous material "Erica Frank John Ken Lee Loweye Sam Tyrone" where Stata expects to find {.

In other words, when using -foreach..local- you put the name of the local macro between -local- and -{-; you do not list out the contents of the local macro there.

[Rhina Torres]

Thank you for helping me understand my issue Clyde Schechter .The code was still unable to run, so I came up with a more manual code that opened each sheet and saved its content. How can I simplify the code below using a loop?



import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Erica") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Erica_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Frank") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Frank_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("John") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_John_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Ken") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Ken_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Lee") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Lee_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Loweye") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Loweye_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Sam") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Sam_11-17-23.dta", replace

import excel "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx", sheet("Tyrone") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_Tyrone_11-17-23.dta", replace


*put all new assignments into one file
use "IAP_Assignments_Erica_11-17-23.dta", clear
append using "IAP_Assignments_Frank_11-17-23.dta"
append using "IAP_Assignments_Frank_11-17-23.dta"
append using "IAP_Assignments_John_11-17-23.dta"
append using "IAP_Assignments_Ken_11-17-23.dta"
append using "IAP_Assignments_Lee_11-17-23.dta"
append using "IAP_Assignments_Loweye_11-17-23.dta"
append using "IAP_Assignments_Sam_11-17-23.dta"
append using "IAP_Assignments_Tyrone_11-17-23.dta"
sort daterequested
save "Draft_IAP_11-17-23.dta", replace

[Nick Cox]

This may help. Naturally I can't test anything. Presumably you don't want two copies of Frank's observations.

local file "C:\Users\rtorres43\OneDrive - NYCDOE\Documents\PSAL Data_Desktop\IAP Outstanding Assignments\IAP Outstanding Assignments 11-17-23.xlsx"

local people Erica Frank John Ken Lee Loweye Sam Tyrone 

foreach name of local people {  

import excel "`file'" , sheet("`name'") firstrow case(lower) allstring clear
keep if assignmentstatus=="Complete"
save "IAP_Assignments_`name'_11-17-23.dta", replace

}

local people Erica Frank John Ken Lee Loweye Sam 

foreach name of local people { 
    append using "IAP_Assignments_`name'_11-17-23.dta"
}

sort daterequested
save "Draft_IAP_11-17-23.dta", replace