BACKGROUND: The North Carolina Injury Epidemiology and Surveillance Unit produces over 100 injury and violence related factsheets and surveillance reports each year. Generating these products can be a time-consuming process, requiring compilation of data from several sources, analysis in SAS, and creation of figures and print-friendly formatting for each update. Automation of this process using Excel formulas and links has improved timely communication of surveillance efforts.
METHODS: Microsoft Excel was used to automate templates for North Carolina Violent Death Reporting System (NC-VDRS) factsheets, Prescription Drug Overdose (PDO) surveillance reports, and a PDO PowerPoint slide deck. Data outputs from SAS or external sources were copied to unique worksheets in a single workbook which, through a series of formulas, automatically fed into a summary sheet with figures and text formatted for printing. Four formulas: SUM, COUNT, IF, and VLOOKUP, were the backbone of the automation. Free text boxes and figures were also automated to update. Minor changes, such as dates of analysis and name of county, still require manual updates. Figures were linked to a paired PowerPoint to automatically update for each of North Carolina’s 100 counties.
RESULTS: Previously, a single NC-VDRS factsheet would take upwards of 3 hours to produce after data was analyzed; automation has reduced that time to 30 minutes resulting in a total time-saving of 60 hours for these products alone. PDO surveillance reports, which integrate 7 years of statewide emergency department data on overdose, likewise take approximately 15 minutes to create. After approximately one hour to update the feeding worksheets, generating a PowerPoint slide deck with county-level PDO death data on rate, intent, type of drug, and demographics takes less than 30 seconds.
CONCLUSIONS: Initially, the automation process is timely endeavor and requires significant time investment to familiarize staff with the use of formulas, format worksheets, and perform quality assurance. However, after automation formulas and codes are in place the time and effort required to update NC-VDRS factsheets was drastically reduced. The reduced burden of completing communication materials allows time to be redirected to new projects, or for responding to specific data requests. Indeed, the success of the NC-VDRS automation spurred the creation of additional automated products, the PDO surveillance reports and the county-level PowerPoint, which are used for presentations by leadership across the state.