Using local macro to export multiple datasets to multiple sheets in a single Excel workbook

[Ian Gabriel]

Hello Statalist,

My goal is to compile multiple .dta files that all live in the same directory (and are the only files in that directory) into a single Excel workbook with one tab/sheet per .dta file.

The files all end in either 2010_2019.dta or 2012_2019.dta.

I'm having two issues:

1. the local I am using does not appear to be storing the file information for the .dta files
2. I can't figure out the code for the export to Excel part

I am using the following code:

cd "C:\Users\ianbg\Desktop\LTR\STATA\Community Profiles\Community Profiles Calculations\2019 Calculations\datasets"

local files : dir . files `" *2010_2019 *2012_2019 "'
di `files'

foreach file of local files {
    use `file', clear
    export excel using `file', firstrow(varlabels) replace
}

This returns "end of do-file", but does not produce the spreadsheets. The -display command also returns nothing -- appears to be empty (issue 1 above).

Also, I realize that the above code, if working properly, would export individual excel spreadsheets for each .dta -- any advice on how to adjust to address issue 2 above?

Thanks very much!
Ian

[Clyde Schechter]

You probably think your -local files:...- command is going to identify files that match either the pattern *2010_2019 or *2012_2019. But it will not. The -local files: ...- command can accept only one pattern at a time. Moreover, even if you separate this into two separate -local files: ...- commands and then concatenate the resulting file lists, you still will come up empty because a file like abc_2010_2019.dta is not a match for *2010_2019. The latter specifies that there is nothing following the *2010_2019.

Now, in this case, I think there is a very easy solution. You stated in #1 that the .dta files you are looking for are exactly the .dta files in that directory. So:

local files: dir "." files "*.dta"

will get you exactly what you want.