Mass producing appendices using Stata and word processor mail merge

1. Mass producing appendices using Stata and word processor mail merge NASUG July 11, 2005 by: Michael Blasnik M. Blasnik & Associates, Boston, MA

2. Background • Needed to produce appendix containing some data, analysis results, and graphs for each of 186 “panels” in a panel dataset • Panels= refrigerators: project involved assessing energy performance of refrigerators in 186 homes in New England on behalf of three utilities • Produce one page summary for each unit that includes 3 graphs -- total of 186 pages and 558 graphs • Wanted to be able to easily update and revise – no one-by-one inserting/pasting of data or graphs

3. Overall Approach • Use “mail merge” facility of word processor, like a 1 page form letter • Use Stata to automatically create all of the graphs (exported to usable format such as .emf in Windows) and write needed data out to tab-delimited text file using outsheet • Use Word Processor Mail Merge to lay out page, insert references to data fields in .txt file, and include references to the names of the graphs created in graph fields

4. Specific Approach: Stata • Use do file with loop to create graphs for each panel and export (.emf) with consistent naming. • Within loop can place panel specific data into macros to make custom titles, reference lines, etc. in the graphs • Use xsize() and ysize() to make graphs fit page layout • Need to deal with missing data, especially missing graphs (create null graph saying “No Data” using twoway scatteri) • Create dataset with 1 observation per panel containing all data/results needed in appendix, formatted as desired • Include graph file names as variables in the dataset, or a variable that can be used to construct graph file names, such as id###_g1.emf, id###_g2.emf, etc.. • outsheet dataset to tab delimited format (readable as mail merge data source)

5. Specific Approach: Stata Sample Stata Code qui levels site, local(sitelist) foreach site of local sitelist { ...*prepare stuff for graphs graph ......., saving(`site'_g1.gph, replace) graph export `site'_g1.emf, replace graph .........., saving(`site'_g2.gph,replace) graph export `site'_g2.emf, replace graph .........., saving(`site'_g3.gph,replace) graph export `site'_g3.emf, replace } outsheet site othervar1 othervar2... using mergedata.txt

6. Specific Approach: Word Processor Warning: instructions based on MS Word, but word processors and page layout programs not from the evil empire can also do this using slightly different commands • Create 1 page layout to hold data and graphs • Using tables tends to work well for placement • Turn on mail merge toolbar or use “wizard” (in MS Word) • Identify .txt file created by outsheet as mail merge data source • Insert data from .txt file into proper places in document by using Insert->Field->Mergefield • Insert graphs using Insert->Field->Includepicture and grab any picture file (.emf file) (use // and enclose in quotes when typing) • Key Trick: edit IncludePicture field to make it work from mail merge • Use alt-F9 to toggle view of field codes • insert Mergefield within IncludePicture field to create graph file name: {INCLUDEPICTURE "../../DATA/MYGRAPHS/{MERGEFIELD "sitename"}_g1.emf"}

7. Inserting Data: Code

8. Inserting Graphs: code

9. Inserting Data: Results

10. Inserting Graphs: Results

11. It worked… - didn’t take long to set up- very fast to update and revise as needed/wanted