Looping conditional formatting and outputting to multiple Excel files

[Sarah Eliason]

I've been using Stata for a few years now, but am fairly new to macros and loops.

I am creating individual Excel reports for 420 different geographical areas. I need a loop that will create an Excel file using the area name, and then use putexcel to insert all of the statistics for that country. For instance: I need create an Excel file named "ArgentinaBuenosAiresEast.xlsx", then save the statistics of that area under that file name, then do the same on a new Excel file for Argentina Buenos Aires North. I have created a loop using if statements that give me the statistics I need for each area, I'm just not sure how to simultaneously set a new putexcel file with the names I need each time.

I have both a string and a categorical version of the area variable, and after reading up, it seemed like "tokenize" may give me what I'm looking for. I've recreated an abbreviated version of my code below, along with the output. It seems to run, but gives me an "invalid file specification" error:

sysuse auto

levelsof make, local(make)
tokenize "`make'"
foreach num of numlist 1/5 { 
putexcel set "`1'".xlsx, sheet(Sheet1) modify
putexcel A1:D3, merge 
putexcel E1:F1 = "Foreign:", merge bold
    tab foreign if rep78==`num', matcell(cellcounts)
    putexcel g1 = `r(N)', hcenter bold
    }

OUTPUT
. foreach num of numlist 1/5 { 
  2. putexcel set "`1'".xlsx, sheet(Sheet1) modify
  3. putexcel A1:D3, merge 
  4. putexcel E1:F1 = "Foreign:", merge bold
  5.         tab foreign if rep78==`num', matcell(cellcounts)
  6.         putexcel g1 = `r(N)', hcenter bold
  7.         }
invalid file specification

[William Lisowski]

Welcome to Statalist, Sarah. Thank you for presenting an excellent reproducible example.

You had two problems in your code. The first is that your tokenize statement wasn't quite right, but I think you most have miscopied something you later corrected. And the second is in your putexcel set command, which is where the error is coming from.

Here's a simplified example that should point you in the right direction

sysuse auto, clear
levelsof make, local(make)
tokenize `"`make'"'
foreach num of numlist 1/5 { 
    putexcel set "``num''.xlsx", sheet(Sheet1) modify
    putexcel E1:F1 = "Foreign:", merge bold
    }

and here is its output

file AMC Concord.xlsx saved
file AMC Pacer.xlsx saved
file AMC Spirit.xlsx saved
file Audi 5000.xlsx saved
file Audi Fox.xlsx saved

Before I changed `1' to ``num'' it repeatedly used AMC Concord. But the key to avoiding your error message was to extend the quotation marks surrounding the filename to include the suffix.

As a bonus answers to questions you didn't ask, you could loop through all the makes with

sysuse auto, clear
levelsof make, local(make)
foreach m of local make { 
    putexcel set "`m'.xlsx", sheet(Sheet1) modify
    putexcel E1:F1 = "Foreign:", bold
    }

or avoid the tokenize while only doing the first 5 with

sysuse auto, clear
levelsof make, local(make)
forvalues num = 1/5 {
    local m : word `num' of `make'
    putexcel set "`m'.xlsx", sheet(Sheet1) modify
    putexcel E1:F1 = "Foreign:", bold
    }

[Sarah Eliason]

William,
Thank you for your response. I've copied your code and run it in my Stata, and it creates the macro and the token perfectly, but gives me the following output:

. foreach num of numlist 1/5 {
  2.     putexcel set "``num''.xlsx", sheet(Sheet1) modify
  3.     putexcel E1:F1 = "Foreign:", merge bold
  4.     }
could not write merge format to file

I receive similar problems with the other two code options. The first produces output:

. foreach n of local(make) {
  2. putexcel set "`n'.xlsx", sheet(Sheet1) modify
  3. }
_( invalid name

The second appears to run, but does not say that it saved the file anywhere and I can't seem to find it

. foreach m of local make {
  2.     putexcel set "/Users/eliasonsa/Documents/SafetyReports/`m'.xlsx", sheet(She
> et1) modify
  3.     putexcel E1:F1 = "Foreign:", bold
  4.     }

Additionally, the numlist that I use in the loop is intended to be used to filter the data. I realized that a better sample code would be one where I have an encoded make and used it to filter the successive data. I've attached that here, note the tab foreign located under the creation of the title, "Foreign":

sysuse auto, clear    
encode make, gen(make_encode)
levelsof make, local(make)
tokenize `"`make'"'
foreach num of numlist 1/74 {
    putexcel set "``1''.xlsx", sheet(Sheet1) modify
    putexcel E1:F1 = "Foreign:", merge bold
    tab foreign if make_encode==`num', matcell(cellcounts)
    putexcel g1 = `r(N)', hcenter bold
    }

I'm operating Stata 15 on macOS Sierra, I'm not sure if this has a bearing on why your code may not be functioning for me. I'm also wondering if using `num' in the file name will impact my use of numlist later on as a filter for my data.

[William Lisowski]

Away from my Stata for next two weeks, but believe failure to write merge is due to cells already being merged. Delete pre-existing xlsx files and try again.