Type mismatch error when appending excel files

[Scott Rick]

Hi. I am using the code below to append several excel files together. While doing so, I also want to generate a source variable that equals the year mentioned in the file name and drop several variables in the excel files. The files are named as VC_`f', where `f' is the year.

On running the code below, I get error r(109) - type mismatch. Could someone help me understand what is happening here? Thanks!

clear 
tempfile building 
save `building', emptyok  

 foreach f in "2022" "2023" "2024" {

    import excel using "${db}\raw data\CC_primary care\VC_`f'.xlsx", sheet("Sheet1") firstrow
    generate str source = `f'
    
    
    *dropping irrelevant variables
    drop tp_category referralfrom next_of_kin next_of_kin_name phone cell_number aadhar ///
    ration address country mandal_name pin_code tp_log_date tp_log_time time_stamp_for_update ///
    system_name system_code test_metrics
    
    
    append using `building' 
    save `"`building'"', replace 
    }

[Clyde Schechter]

When you run -foreach f ...-, Stata creates a local macro named f. One of the rules of Stata's macro creation is that initial and final quotes are dropped. So although you have written 2022, etc. bound in quotes, Stata discards those quotes. So `f' is set, not to "2022", but to 2022. When you get to your -gen str source = `f'-, it expands to -gen str source = 2022-, and that is your type mismatch.

You can accomplish what you are seeking here by coding it instead as:

foreach f in 2022 2023 2024 {
    ...
    gen str source = "`f'"
    ....
}

That said, if f is always going to be numeric and represent a calendar year, why do you want to store it as a string anyway?

[Scott Rick]

Clyde Schechter Thank you. That solved the problem!