Creating an excel spreadsheet using putexcel and naming sheets using multiple local macros

[James Lee]

Hi Statalist community,

Below is a sample dataset that I made.

            clear all
            set seed 1234
            *Create a dataset of 1000 folks
            set obs 1000
            *Create a id number
            gen id=_n
            *Create random number of groups that individuals are in
            *Individuals could have been in 1 of 3 neighborhood groups
            generate neighborhood_subgroups = runiformint(1, 3)
            label define neighborhood_subgroups 1 "Group 1" 2 "Group 2" 3 "Group 3"
            label values neighborhood_subgroups neighborhood_subgroups
            *Assign age group
            gen age_group = runiformint(1, 4)
            label define age_group 1 "20_21" 2 "22_23" 3 "24_25" 4 "26_27"
            label values age_group age_group
            save practice.dta, replace

I created a spreadsheet with unique sheets that use the numerical values to name the unique subgroups

    clear
    forval t =1/3 {
            forval k =1/4 {
                *use sample data
                use practice.dta, replace
                *subset students by neighborhood and age group
                keep if neighborhood_subgroups==`t' & age_group==`k'
                *produce spreadsheet and name sheets by the subgroup numbers
        putexcel set example.xlsx,  modify sheet("`t', `k'")
                *produce some statistic
                tabstat neighborhood_subgroups, save
                return list
                putexcel A2 = matrix(r(Stat1))
            clear all
            }
        }

Instead of naming the Excel sheets by subgroup numbers, I would like to name them by using the strings in the pz and pd macros that I created. Below is the code that I tried but it did not work.

*first macro with 3 separate strings
    local pz `" "Group 1" "Group 2" "Group 3" "'
*second macro with 4 separate strings
local pd `"   "14_15"  "18_19" "20_21" "25_26" "'
        clear
    forval t =1/3 {
            forval k =1/4 {
                use practice.dta, replace
                keep if neighborhood_subgroups==`t' & age_group==`k'
        putexcel set example.xlsx,  modify sheet("`pz', `pd'")
                tabstat neighborhood_subgroups, save
                return list
                putexcel A2 = matrix(r(Stat1))
            clear all
            }
        }

Ultimately, I want to create a spreadsheet named example.xlsx with 12 sheets named:
"Group 1, 14_15" , "Group 1, 18_19" , "Group 1, 20_21" , "Group 1, 25_26"
"Group 2, 14_15" , "Group 2, 18_19" , "Group 2, 20_21" , "Group 2, 25_26"
"Group 3, 14_15" , "Group 3, 18_19" , "Group 3, 20_21" , "Group 3, 25_26"

Thank you so much for your help.

[Tom Scott]

Maybe a stupid question, but can you create and name the sheets in Excel and then just create a variable with those string values and use levelsof group, local(levels), foreach i of local levels, and then sheet("`i'") modify

[James Lee]

Hi @Tom Scott. Thanks for feedback. I have over 100 different sheets that I would to name. I am trying to automate this using some loops.

[Roman Mostazir]

I don't think you need additional `pd' `pz' when you can directly call the value labels for neighourhood and age group and name the excel sheets using those labels. By the way, the tabstat command saves matrix as r(StatTotal)

    forval t =1/3 {
        loc namepart1 : lab neighborhood_subgroups `t'    
            forval k =1/4 {
                loc namepart2 : lab age_group `k'              
                use practice.dta, clear              
                keep if neighborhood_subgroups==`t' & age_group==`k'
                putexcel set example.xlsx,  modify sheet("`namepart1',`namepart2'")
                tabstat neighborhood_subgroups, save
                return list
                putexcel A2 = matrix(r(StatTotal))
            }
      }

I also think you could avoid the unnecessary 'keep' command while you can directly run the 'tabstat' command with if qualifier and referring to the `t' and `k' local macros.

[James Lee]

@Roman Mostazir Thank you so much for your help. The code worked really well. Also thank you for helping me make my code simpler. Using the if qualifier reduces the complexity.