Problem loop over years & exporting matrix

[Flor Carosa]

I have one database for a given country with data from 2006 to 2021. I want to export an xlsx where each column represents a year and each row represents one of the four local “l_indicators”. I tried the following code, but it is failing because I obtain the same value for each row – that is, it is processing the average for the whole dataset instead of per year. I understand there is a problem with the year loop, but I can’t figure out how to solve it.
You can find the code below and a screenshot of the xlsx that I am obtaining as a result. Please let me know if further clarifications are needed - Any suggestions will be extremely helpful.
Thank you!

local MAT "TOT MEN WOM TOT_MIG MEN_MIG WOM_MIG TOT_NAT MEN_NAT WOM_NAT"

local l_indicators "LFP UNL DW isei"

foreach x of local MAT{
    mat `x'=J(4,16,.)
}

    local j=1    
    forval year=2006/2021 {
    local f=1
    foreach i of local l_indicators {
    
        qui summ `i' [w=weight] //Total
        mat TOT[`f',`j'] = r(mean)
        qui summ `i' [w=weight] if sex==1 // MEN
        mat MEN[`f',`j'] = r(mean)
        qui summ `i' [w=weight] if sex==0 // WOMEN
        mat WOM[`f',`j'] = r(mean)    
        qui summ `i' [w=weight] if native==0 // Migrants
        mat TOT_MIG[`f',`j'] = r(mean)            
        qui summ `i' [w=weight] if sex==1 & native==0  // Migrant men
        mat MEN_MIG[`f',`j'] = r(mean)        
        qui summ `i' [w=weight] if sex==0 & native==0  // Migrant women
        mat WOM_MIG[`f',`j'] = r(mean)    
        qui summ `i' [w=weight] if native==1 // Natives
        mat TOT_NAT[`f',`j'] = r(mean)
        qui summ `i' [w=weight] if sex==1 & native==1  // Native men
        mat MEN_NAT[`f',`j'] = r(mean)
        qui summ `i' [w=weight] if sex==0 & native==1  // Native women
        mat WOM_NAT[`f',`j'] = r(mean)

        local ++f
        }
   local ++j    
   }



foreach x of local MAT {
    drop _all
    svmat `x'
    
    tempfile `x'file
    save     ``x'file'
    
    local ctry: word `f' of `databases'
    local ctry = substr(`"`ctry'"', 1, 2)
    
    export excel "`ctry'_stats", sheet("`x'", modify) firstrow(variables) 
}

The excel file I get:

[Clyde Schechter]

Your code inside the loop never refers to the year, so it applies to all years.

qui summ `i' [w=weight] //Total
// SHOULD BE
qui summ `i' [w=weight] if year == `year'

// AND
qui summ `i' [w=weight] if sex==1 // MEN
// SHOULD BE
qui summ `i' [w=weight] if sex==1 & year == `year'

// ETC.

[Flor Carosa]

I was sure it was some silly thing, but sometimes you just need another pair of eyes to spot it... thank you so much, Clyde! Solved