using multiple foreach loops to loop through strings

[James Lee]

Hi Statalist community,

I have a Excel workbook with 56 worksheets. Each worksheet has the same variables but they are disaggregated by education and age. I am trying import 1 worksheet at a time, do something within the worksheet, and then export it to a new Excel workbook. Based on the Statalist suggestions, I am not going to upload a Excel spreadsheet. Below is code that I develop that does what I want.

forval j = 1/56 {
clear all
*create a local
local period `" "G1 high_school, 18_19" "G1 high_school, 20_24" "G1 high_school, 25_29" "G1 high_school, 30_39" "G1 high_school, 40_49" "G1 high_school, 50_59" "G1 high_school, 60_69" "G2 some_college, 18_19" "G2 some_college, 20_24" "G2 some_college, 25_29" "G2 some_college, 30_39" "G2 some_college, 40_49" "G2 some_college, 50_59" "G2 some_college, 60_69" "G3 associates_degree, 18_19" "G3 associates_degree, 20_24" "G3 associates_degree, 25_29" "G3 associates_degree, 30_39" "G3 associates_degree, 40_49" "G3 associates_degree, 50_59" "G3 associates_degree, 60_69" "G4 ba_bas, 18_19" "G4 ba_bas, 20_24" "G4 ba_bas, 25_29" "G4 ba_bas, 30_39" "G4 ba_bas, 40_49" "G4 ba_bas, 50_59" "G4 ba_bas, 60_69" "G5 masters_degree, 18_19" "G5 masters_degree, 20_24" "G5 masters_degree, 25_29" "G5 masters_degree, 30_39" "G5 masters_degree, 40_49" "G5 masters_degree, 50_59" "G5 masters_degree, 60_69" "G6 phd_degree, 18_19" "G6 phd_degree, 20_24" "G6 phd_degree, 25_29" "G6 phd_degree, 30_39" "G6 phd_degree, 40_49" "G6 phd_degree, 50_59" "G6 phd_degree, 60_69" "G7 post_doc, 18_19" "G7 post_doc, 20_24" "G7 post_doc, 25_29" "G7 post_doc, 30_39" "G7 post_doc, 40_49" "G7 post_doc, 50_59" "G7 post_doc, 60_69" "G8 adult_ced, 18_19" "G8 adult_ced, 20_24" "G8 adult_ced, 25_29" "G8 adult_ced, 30_39" "G8 adult_ced, 40_49" "G8 adult_ced, 50_59" "G8 adult_ced, 60_69" "'
local pd: word `j' of `period'

*import a sheet from the Excel workbook
import excel "C:\Users\sample10_03_2023.xlsx", sheet("`pd'") firstrow

*Do something in the spreadsheet
gen education_age="`pd'"
keep education_age

*export the data to a new spreadsheet
export excel using "C:\Users\sample10_04_2023.xlsx", sheet("`pd'") sheetmodify firstrow(variables) nolabel
clear all
}

I created a local that includes the name of each of the 56 worksheets. However, this invites a lot of error and I am trying to streamline it. Below is what I tried to do but I seem to be missing something. I tried updating my code by using several foreach statements and created two locals to manage the string lists but I was not able to get this to run. Any suggestions would be great. Thank you so much for your help.

local education high_school some_college associates_degree ba_bas masters_degree phd_degree post_doc
local age 18_19 20_24 25_29 30_39 40_49 50_59 60_69
forval i = 1/8 {
foreach e of local education {
    foreach p of local age {
        import excel "C:\Users\sample10_03_2023.xlsx", sheet("`G`i' `e', 'p'") firstrow

*Do something in the spreadsheet
gen education_age="`G`i' `e', 'p'"
keep education_age


*export the data to a new spreadsheet
export excel using "C:\Users\sample10_04_2023.xlsx", sheet("`G`i' `e', 'p'") sheetmodify firstrow(variables) nolabel
clear all

        }
    }
}

[Daniel Feenberg]

Use -set trace on- and -set tracedepth 2- before the loops to see exactly what they are evaluating to. See https://www.stata.com/manuals13/ptrace.pdf

[Nick Cox]

keep education_age

means that you are dropping everything else that you imported.

[James Lee]

@Daniel Feenberg

Thanks for your help Daniel. I tried to use the trace option but I gave up and I eventually used two forval loops. This is how I did it. There probably is better way with foreach loops but this will suffice.

forval education_index = 1/7 {
forval age_index 1/7 {
clear all
*Create locals
local education high_school some_college associates_degree ba_bas masters_degree phd_degree post_doc
local age 18_19 20_24 25_29 30_39 40_49 50_59 60_69

*Establish the order of the index
local edu_desc: word `education_index' of `edu_groups'
local age_desc: word `age_index' of `age_groups'
 
*Import the data
import excel "C:\Users\sample10_03_2023.xlsx", sheet("`edu_desc', 'age_desc'") firstrow

*Do something in the spreadsheet. 
gen education_age="`edu_desc', 'age_desc'"
keep education_age


*export the data to a new spreadsheet and unique worksheet within this new spreadsheet
export excel using "C:\Users\sample10_04_2023.xlsx", sheet("`edu_desc', 'age_desc'") sheetmodify firstrow(variables) nolabel
clear all
        }
    }

@Nick Cox

For the purpose of this example, I wanted to import a lot of worksheets and execute some analysis and then export it to a different Excel spreadsheet. For the actual analysis, I am creating a lot more variables. This example would of been clearer if I could attach an Excel spreadsheet. Sorry for the confusion.