Looping through cell option in export excel

[Kavitya Sarma]

I would like to write an excel sheet that lists the make of cars in sysuse auto by their trunk space. For example:

5	6	7	8	9
Honda Civic	Datsun 200	Chev. Monza	Datsun 210	Merc. Bobcat
		Pont. Sunbird	Datsun 510	Toyota Corolla
		Pont. Firebird	Datsun 810	Ford Fiesta
			Dodge Colt	Chev Chevette
			Plym. Sapporo

To do this, I have made a local with excel column names and have tried to loop through values of trunk:

sysuse auto, clear
local excel_columns

* make a list of excel column names
foreach first in "empty" "A" "B" "C" "D" "E" "F" "G" "H" "I" "J" "K" "L" "M" "N" "O" "P" "Q" "R" "S" "T" "U" "V" "W" "X" "Y" "Z" {
    foreach second in "A" "B" "C" "D" "E" "F" "G" "H" "I" "J" "K" "L" "M" "N" "O" "P" "Q" "R" "S" "T" "U" "V" "W" "X" "Y" "Z"  {
        if "`first'" == "empty" {
            local excel_columns "`excel_columns'" "`second'1"
        }
        if "`first'" ~= "empty" {
            local excel_columns "`excel_columns'" "`first'`second'1"
        }
        
    }
}

* this needs to start at 2 because the first 'word' of `excel_columns' is a space
local counter = 2

* make a local with the values of trunk 
levelsof trunk, local(trunk_sizes)

foreach size in `trunk_sizes' {

        * make local column with the excel column that corresponds to the iteration of the loop
        local column : word `counter' of `excel_columns'
        * check that the correct column is being stored
        disp "`column'"
        preserve
        * label variable so that it can be column header
        label variable make "Makes with trunk size: `size'"
        * keep only entries that have the trunk size for the loop we are on
        keep if trunk == `size'

        * export the make value to the column stored in the local 
        quietly: export excel make using "..\Data\test.xlsx", sheet("Makes by trunk size") sheetmodify cell(``column'') firstrow(varlabels)
        restore

        local ++counter
}

The column local properly displays the next column A1, B1, C1, etc..., but the excel that is produced only has data in the A column with the names of the makes from the last trunk value.

Any advice on how should I be using the local column value in the cell option?

[Clyde Schechter]

I could explain what is going wrong with your code, but it's pretty lengthy and complicated. It boils down to the way you are using quotes in your local macro definitions. But, in any case, the approach you are taking is unnecessarily complicated. You can do this with:

sysuse auto, clear

local i 1

levelsof trunk, local(sizes)
foreach s of local sizes {
    local column: word `i' of `c(ALPHA)'
    label var make "Makes with trunk size: `s'"
    export excel make if trunk == `s' using test.xlsx, ///
        sheet("Makes by Trunk Size", modify) cell(`column'1) ///
        firstrow(varlabels)
    local ++i
}

I've run this on my setup and it produces the results you are looking for.

Note: I have placed the -modify- suboption inside -sheet()- rather than having the separate -sheetmodify- option you used. -sheetmodify- is older syntax; I believe it still works, but it is deprecated.

One limitation on this code: it would not work correctly if there were more than 26 distinct values of the variable trunk.

[Kavitya Sarma]

Thank you very much for your response Clyde Schechter.

My actual need case has about 200 distinct values, so I have expanded on the code you provided.

 sysuse auto, clear   ​​​​​local i 1 local j 0  levelsof trunk, local(sizes)   foreach s of local sizes {      local column0          if `j' > 0 {         local column0 : word `j' of `c(ALPHA)'     }      local column1: word `i' of `c(ALPHA)'      label var make "Makes with trunk size: `s'"          export excel make if trunk == `s' using test.xlsx, sheet("Makes by Trunk Size", modify) cell(`column0'`column1'1) firstrow(varlabels)      local ++i      if `i' > 26 {         local i 1         local ++j     }   }

Thank you again for your help, I am a big fan of yours. -Kavitya

[Kavitya Sarma]

Thank you very much for your response Clyde Schechter

My actual need case has about 200 distinct values, so I have expanded on the code you provided.
Code:

sysuse auto, clear

local i 1
local j 0
levelsof trunk, local(sizes)

foreach s of local sizes {

    local column0
    if `j' > 0 {
         local column0 : word `j' of `c(ALPHA)'
    }

    local column1 : word `i' of `c(ALPHA)'

    label var make "Makes with trunk size: `s'"
    export excel make if trunk == `s' using test.xlsx, sheet("Makes by trunk size", modify) cell(`column0'`column1'1) firstrow(varlabels)


    local ++i

    if `i' > 26 {
         local i 1
         local ++j
    }

}

Thank you again for your help, I am a big fan of yours. -Kavitya

[Clyde Schechter]

Nice!

Unfortunately, your code did not format well in the Forum editor's code box, it is all spread out over one very long line, and it also is "contaminated" with non-printing characters that throw errors on nearly every line. (None of this is your fault, I believe. It just seems to happen sometimes when we paste code in. I don't know why.)

Anyway, I've cleaned it up so it can be copied into the do-editor and will run as is. And I've changed it to do things by price instead of trunk size so that people can see how it actually works with more than 26 columns in play, and so when somebody has a similar problem in the future they can mark up your code and go with it.

sysuse auto, clear                                               
local i 1                                                        
local j 0                                                        
levelsof price, local(sizes)                                     
foreach s of local sizes {                                       
    local column0                                                   
    if `j' > 0 {                                                    
            local column0 : word `j' of `c(ALPHA)'                         
    }                                                               
    local column1: word `i' of `c(ALPHA)'                           
    label var make "Makes with price: `s'"                          
    export excel make if price == `s' using test.xlsx, ///          
            sheet("Makes by Price", modify) cell(`column0'`column1'1) ///  
            firstrow(varlabels)                                            
    local ++i                                                       
    if `i' > 26 {                                                   
            local i 1                                                   
            local ++j                                                      
    }                                                               
}