How to Create an Analytic File

Introduction

Converting raw data into research insights requires the creation of an analytic file – a refined dataset customized for a specific study. Building these files involves aggregating large-scale data into target units, such as hospitals(1) or individual patients(2). Though the process is complex, it is a cornerstone of well-conducted research. This article offers practical guidance and key considerations for constructing these essential datasets.

Advantages of a Dedicated Analytic File

There are many valuable reasons to create an analytic file for a research project including:

• Run-time on a condensed analytic file will be much faster than on large datasets. Some data environments have limited resources. Repeatedly processing large datasets may have adverse effects on other users.
• Code complexity is more simplified and easier to follow when unobscured by repeated filtering and aggregation commands. A better way is to do all filtering and aggregation one time and create a permanent analytic file. This file can be thoroughly checked for accuracy and once completed, those checks do not need to be done again.
• Removes “noise” when reviewing output (IE: having a smaller dataset will visually help you identify if your indicators are being calculated correctly or not).
• Avoids inadvertent corruption of the original data.
• Removes “noise” when reviewing output (IE: having a smaller dataset will visually help you identify if your indicators are being calculated correctly or not).
• Avoids inadvertent corruption of the original data.

Key Considerations

Key considerations and tips for building these files include:

• Mapping the desired content and structure of the analytic file ahead of time helps keep the task focused. The map will help determine which data files will be needed to create the analytic file.
• Refer to data dictionaries and code books often for variable descriptions, code values, formatting and special notes that may help understand the variable.
• Analysts are wise to annotate their programs/code thoroughly so that it is easier for themselves and others to adjust and rerun as new understanding of the data emerges.
• The required variables and logic for synthesizing information may be different for different data files.
• You may come across conflicting information. For example, differing demographic information can exist in claims data verses the Master Beneficiary Summary File (MBSF). Defer to the more reliable data source if one can be determined (in this case, the MBSF).
• Consider how data decisions might affect results. Sensitivity analyses are a good option for determining how much of an impact one decision has versus another.

Guidance for Construction

Processing data typically does not go directly from point A to point Z - it tends to be an iterative process. As we dig into the data and gain a better understanding of it, we may decide to redo previous steps or change how we move forward.

These steps follow a general sequence, but the process is iterative. You may find yourself revisiting or jumping between stages as your work progresses:

• Combine/join the files that contain the variables/data you need.
  • For example, hospital claims associated with ER visits can be found in Inpatient and Outpatient RIFs, and physician claims for the same ER visits are found in Carrier RIFs.
  • Sometimes more than one level (line detail + base claim information) or more than one file type will need to be combined to create one variable (e.g., identifying physician visits that occurred during a hospitalization).
• Limit data to your target universe.
  • Select records based on your research criteria. Commonly used criteria include:
    • Service dates
    • Demographic characteristics
    • Enrollment
    • Diagnoses
    • Procedures
  • Consider removing miscellaneous unwanted records, for example:
    • Denied claims (for more information about denied claims see the article Claim Denials in Medicare FFS RIFs.
    • Chart reviews in encounter data
  • Remove or combine records for the same service (i.e., duplicates)
    • For example, If ER claims from inpatient, outpatient and carrier files were stacked on top of each other, there would most likely be multiple records from the same visit. To get an accurate ER visit count, records must be deduplicated or consolidated.
      • Records for the same visit/stay should have overlapping dates. If dates are close but do not match exactly, users may want to consider applying a “fuzzy” date range test, for example, within a day or two.
• Construct measures and flags.
• Remove unnecessary variables.
• Summarize/aggregate data up to the unit of analysis. Common aggregation steps include:
  • Summing financial amounts
  • Counting claims
  • Building indicator variables (0/1). For example, members who had a certain diagnosis within a defined timeframe will be given a 1, all others a 0.
  • Calculating spans of time before/after an event. For example, to calculate readmissions, you may want to create an analytic data set of index admissions and their associated readmissions along with variables that tell you the number of days in between, or other characteristics.
  • Combinations of the above: for example, count hospitalizations that occur within a given time span.

Example

Table 1 illustrates the structure of an analytic file constructed from CMS fee-for-service inpatient claim RIFs(3) for analyzing hospital readmissions. Each record represents one index admission. If the patient was readmitted within 30 days of discharge, information from that stay was attached. The file was enhanced by attaching patient demographics from the Master Beneficiary Summary File (MBSF)(4).

Hospitals occasionally submit separate claims for different spans of a single stay. This is more common for long stays. INDEX_ADMISSION_ID is a unique key that was constructed by combining the three claim fields BENE_ID, ADMSNT_DT, and PRVDR_NUM to identify claims from the same stay. Different choices could go into making the key, it is up to each researcher to decide what to include. For this example, multiple claims having with the same INDEX_ADMISSION_ID were deduplicated or consolidated into one record.

Table 1. Example of an analytic file

When consolidating multiple claims into a single stay-level record, researchers may encounter conflicting information across records. Resolving these discrepancies requires establishing rules for determining the data points that will go into the analytic file. For instance, if discharge dates differ across claims with matching keys, selecting the latest chronological discharge date could be a reasonable choice. Another strategy is to prioritize information from the most recent claim – defined by the latest claim through or submission date – under the assumption that it contains the most comprehensive and adjudicated data. Ultimately, the choice of logic depends on the study's priorities and should be documented to ensure reproducibility.

Dummy variables (0/1) flag when the patient was readmitted to the hospital (Readmission_30) through the ER (ER_ind). These indicators were constructed using a combination of claim information from the index admission, the readmission and the ER visit (where applicable). Beneficiary characteristics (age, zip) from the MBSF were attached via BENE_ID.

Conclusion

Creating a refined analytic file is an essential bridge between raw administrative claims and meaningful research insights. The transformation process requires navigating complex data structures, resolving conflicting information, and making sound methodological decisions, resulting in a stable and efficient foundation for analysis.