Problem export excel and matrices

[Flor Carosa]

Hello everyone,
I am having trouble with the export excel command and I would really appreciate your help.

I am running a loop through 30 databases and I want to export results to an excel with nine sheets (one for each MAT local), 2 rows (one for each lab local) and 30 columns (one for each database). Nonetheless, everytime I run this code I obtain an excel with nine sheets, two rows but only the cells in the last column have data, the rest of the columns have the variable name but are empty. Any help to try to spot the mistake will be highly appreciated!

PS: First post in the forum, so apologies if any point is not clear enough in my question!

local MAT "TOT MEN WOM TOT_M MEN_M WOM_M TOT_N MEN_N WOM_N"

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

local lab "works dw"

local databases : dir "." files "*2021Q4.csv"

foreach db in `databases' {
    
    import delimited "`db'", clear

    local prefix = substr("`db'", 1, 2)

        local f=1
        foreach i of local lab {

        qui summ `i' [w=weight] if `i' == 1 
        mat TOT[`f',30] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==1 
        mat MEN[`f',30] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==2 
        mat WOM[`f',30] = r(sum_w)    
        qui summ `i' [w=weight] if `i' == 1 & nat==0 
        mat TOT_M[`f',30] = r(sum_w)            
        qui summ `i' [w=weight] if `i' == 1 & sex==1 & nat==0  
        mat MEN_M[`f',30] = r(sum_w)        
        qui summ `i' [w=weight] if `i' == 1 & sex==2 & nat==0  
        mat WOM_M[`f',30] = r(sum_w)    
        qui summ `i' [w=weight] if `i' == 1 & nat==1 
        mat TOT_N[`f',30] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==1 & nat==1  
        mat MEN_N[`f',30] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==2 & nat==1  
        mat WOM_N[`f',30] = r(sum_w)
            
        local ++f
    }

}

foreach x of local MAT {
    drop _all
    svmat `x'
       
    
    export excel "${folder2}/dsc_stats", sheet("`x'", modify) firstrow(variables) 

}

[Flor Carosa]

Just to clarify: what I think is happening is that each loop overwrites the excel, so it ends up saving only the results for the last database processed

[Clyde Schechter]

There are a few problems I can spot here.

1. You reset local macro f to 1 inside the inner loop, so you are always storing all of your results in row 1: your -local ++f- command is ineffective because on the next iteration of the inner loop, f is reset to 1. If -local ++f- were effective, f would, after the second iteration of the loop, have values that are out of range for the first subscript in these matrices. Since it seems you want `f' to run from 1 to 30, it has to be incremented in the outer loop, not the inner one, and initialized before the outer loop. Maybe better still, make the outer loop -forvalues f = 1/30 {- and then pull the filename out of local macro databases.
2. All of the -mat whatever = - commands refer to column 30. That can't be right.
3. 2 and 3 together imply that each of your matrices will always have missing values everywhere except in element [1, 30]
4. You define a local macro prefix, but you never use it for anything.
5. Your definition of local lab contains works and dw. But then in your -foreach i of local lab {- loop, you condition calculations on `i' == 1,which means it depends on whether the first observation of works or dw (depending on which iteration we are in) is 1--that is legal but I strongly doubt it's what you want.

I don't know exactly what you are trying to do here, but I think that probably you need to do something like the following:

local MAT "TOT MEN WOM TOT_M MEN_M WOM_M TOT_N MEN_N WOM_N"

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

local lab "works dw"

local databases : dir "." files "*2021Q4.csv"

forvalues f = 1/30 {
    local db: word `f' of `databases'
    
    import delimited "`db'", clear

//     local prefix = substr("`db'", 1, 2)

        forvalues i = 1/2 {
            
        local vble: word `i' of `lab'

        qui summ `vble' [w=weight]  
        mat TOT[`i', `f'] = r(sum_w)
        qui summ `vble' [w=weight] if  sex==1
        mat MEN[`i', `f'] = r(sum_w)
        qui summ `vble' [w=weight] if  sex==2
        mat WOM[`i', `f'] = r(sum_w)    
        qui summ `vble' [w=weight] if  nat==0
        mat TOT_M[`i', `f'] = r(sum_w)            
        qui summ `vble' [w=weight] if  sex==1 & nat==0  
        mat MEN_M[`i', `f'] = r(sum_w)        
        qui summ `vble' [w=weight] if  sex==2 & nat==0  
        mat WOM_M[`i', `f'] = r(sum_w)    
        qui summ `vble' [w=weight] if  nat==1
        mat TOT_N[`i', `f'] = r(sum_w)
        qui summ `vble' [w=weight] if  sex==1 & nat==1  
        mat MEN_N[`i', `f'] = r(sum_w)
        qui summ `vble' [w=weight] if  sex==2 & nat==1  
        mat WOM_N[`i', `f'] = r(sum_w)
            
    }

}

foreach x of local MAT {
    drop _all
    svmat `x'
       
    
    export excel "${folder2}/dsc_stats", sheet("`x'", modify) firstrow(variables)

}

I have only low confidence that this will fully resolve your problem, largely because I don't really grasp what you want to do. But I think it will move you in the right direction and perhaps from here you can figure out how to fix it.

[Flor Carosa]

Hello, Clyde,
Thank you so much for your prompt help. Your comments definitely helped me move into the right direction.
Just to clarify, what I wanted to do is to export a file with nine sheets (one for each population sample defined in the local mat), with total stats on two labour market variables (local labs) for 30 countries (the 30 databases)

Now I could export all the results with the following code:

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

foreach x of local MAT{
mat `x'=J(3,30,.)

}

local labs "works dw"

local databases : dir "." files "*2021Q4.csv"

// Loop through each database

local j=1
foreach db in `databases' {
    
    import delimited "`db'", clear

    local prefix = substr("`db'", 1, 2)
     
        local f=1
                foreach i of local labs {

        qui summ `i' [w=weight] if `i' == 1 /
        mat TOT[`f',`j'] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==1 
        mat MEN[`f',`j'] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==2 
        mat WOM[`f',`j'] = r(sum_w)    
        qui summ `i' [w=weight] if `i' == 1 & nat==0 
        mat TOT_M[`f',`j'] = r(sum_w)            
        qui summ `i' [w=weight] if `i' == 1 & sex==1 & nat==0  
        mat MEN_M[`f',`j'] = r(sum_w)        
        qui summ `i' [w=weight] if `i' == 1 & sex==2 & nat==0 
        mat WOM_M[`f',`j'] = r(sum_w)    
        qui summ `i' [w=weight] if `i' == 1 & nat==1 
        mat TOT_N[`f',`j'] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==1 & nat==1  
        mat MEN_N[`f',`j'] = r(sum_w)
        qui summ `i' [w=weight] if `i' == 1 & sex==2 & nat==1  
        mat WOM_N[`f',`j'] = r(sum_w)
        local ++f    

    }
    
local ++j
}


foreach x of local MAT {
    drop _all
    svmat `x'
    
   
    tempfile `x'file
    save ``x'file'

    
    export excel "${folder}/dsc_stats", sheet("`x'", modify) firstrow(variables) 

}

I do, though, have a second issue, related to one of you comments:

4. You define a local macro prefix, but you never use it for anything.

When I export the results, the variable names in the firstrow are the names of the local MAT and a number (e.g., for the first sheet: TOT1, TOT2, TOT3, etc). I would, instead, like to have as variable names the value of the local prefix, which is two words for each country (e.g., FR for France). I've tried using rename but the only prefix that keeps is the one of the last database used - that is, it renames all the variables using SK, instead of the 30 different prefixes. Would you suggest a different approach? Please let me know if I am not clear enough with my question.

Thank you!

[Clyde Schechter]

This is a little confusing. The data being saved in each sheet is an entire matrix that contains information on 30 countries. So prefixing the variable names with information about some countries makes no sense to me. I'm guessing what you mean is that you would like one variable that specifies, in each observation (i.e. corresponding to each row of the matrix) the country represented there. If that's what you want then I think you can do it by modifying the -foreach x of local MAT {- loop as follows:

foreach x of local MAT {
    drop _all
    svmat `x'

   gen country = ""
    order country, first
    forvalues f = 1/30 {
        local cntry: word `f' of `databases'
        local cntry = substr(`"`cntry'"', 1, 2)
        replace country = `"`cntry'"' in `f'
    }
    
  
    tempfile `x'file
    save ``x'file'

    
    export excel "${folder}/dsc_stats", sheet("`x'", modify) firstrow(variables)

}