Bauzon J, Murphy C, Wahi-Gururaj S. Using macros in microsoft excel to facilitate cleaning of research data.
J Community Hosp Intern Med Perspect 2021;
11:653-657. [PMID:
34567457 PMCID:
PMC8462890 DOI:
10.1080/20009666.2021.1954282]
[Citation(s) in RCA: 1] [Impact Index Per Article: 0.3] [Reference Citation Analysis] [Abstract] [Key Words] [Track Full Text] [Download PDF] [Figures] [Journal Information] [Subscribe] [Scholar Register] [Indexed: 12/04/2022] Open
Abstract
Background: Retrospective chart review studies may be delayed by inability to export clean clinical data from an electronic medical record (EMR) or data repository. Macros are pre-programmed procedures that can be used in Microsoft Excel to help streamline the process of cleaning clinical datasets.
Objectives: To demonstrate how macros may be useful for researchers at community hospitals and smaller academic health centers that lack informatics support.
Methods: Using an intrinsic function of our institution’s EMR, vital signs and lab results from 20 individual hospitalizations were exported to a spreadsheet. Two macros were developed to sort through these datasets and output them into a specified format. The speed of macro-assisted data cleaning was compared to manual transcription.
Results: Time spent on data cleaning was significantly reduced when using macro-assisted sorting compared to the manual approach for both vital signs (46.5 seconds versus 12.3 minutes per record, a 94% reduction; P < 0.001) and labs (13.7 seconds versus 2.6 minutes per record, a 91% reduction; P < 0.001).
Conclusions:Macros offer a flexible and efficient tool for cleaning large sets of clinical data, particularly when an institution lacks informatics support or EMR functionality to export clinical data in an analysis-ready format.
Collapse