Creating a variable from the suffix of a workbook name

[Scott Rick]

Hi. I have four excel workbooks, each of which has 31 worksheets within it, one for each month from Jan 2022 to July 2024. Only the suffix of the workbook names vary, to indicate different campuses of a hospital chain. The names of the workbooks are: OC_Jan22_Jul24_KAR, OC_Jan22_Jul24_MTC, OC_Jan22_Jul24_GMRV, OC_Jan22_Jul24_KVC.

While creating one dataset from all four workbooks, I also want to generate a variable that picks up only the suffix of the workbook name. So, the variable should have values, KAR, MTC, GMRV, and KVC. Below is the code that appends all the workbooks to create a single dataset. How can I generate a variable that picks the suffix of the workbook name while doing so? I'd greatly appreciate any help on this.

clear 
tempfile building 
save `building', emptyok  

 foreach f in "KAR" "GMRV" "MTC" "KVC" {

 
    import excel using "${db}\raw data\OC_Jan22_Jul24_`f'.xlsx", describe 
    local n_sheets `r(N_worksheet)'
    *return list local n_sheets `r(N_worksheet)' 
    forvalues j = 1/`n_sheets' { 
    local sheet`j' `r(worksheet_`j')' 
    }    

    forvalues j = 1/`n_sheets' { 
    import excel using "${db}\raw data\OC_Jan22_Jul24_`f'.xlsx", sheet(`"`sheet`j''"') firstrow case(lower) clear 
    qui count if missing(hospital)
    if r(N) {
        display "`=r(N)' observations with missing hospital detected in sheet `j'"
    }
    gen sheet = `j' 
    append using `building' 
    save `"`building'"', replace 
    }

[Andrew Musau]

forvalues j = 1/`n_sheets' {
    import excel using "${db}\raw data\OC_Jan22_Jul24_`f'.xlsx", sheet(`"`sheet`j''"') firstrow case(lower) clear
    qui count if missing(hospital)
    if r(N) {
        display "`=r(N)' observations with missing hospital detected in sheet `j'"
    }
    gen sheet = `j'
    gen workbook="`f'"
    append using `building'
    save `"`building'"', replace
}

??

[Scott Rick]

Thanks Andrew Musau. That worked perfectly!