/********************************************************************************** U.S. Department of Health and Human Services Office of Inspector General (OIG) Office of Evaluation and Inspections Toolkit for Calculating Opioid Levels and Identifying Patients At Risk of Misuse or Overdose: R and SQL Programming Code OEI-02-17-00561, May 2020 This code uses prescription drug data to analyze patients' opioid levels and identify patients who are at risk of opioid misuse or overdose. It is based on the methodology OIG developed for its extensive work on opioid use in Medicare Part D, including its recent data brief entitled, Opioid Use Decreased in Medicare Part D, While Medication-Assisted Treatment Increased (OEI-02-19-00390). This code is being shared as part of the OIG Toolkit: Calculating Opioid Levels and Identifying Patients At Risk of Misuse or Overdose Using R or SQL (OEI-02-17-00561). Please read the full toolkit. It provides important information about preparing the data for the analysis, including how to merge data from the Centers for Disease Control and Prevention. The toolkit may be accessed at www.oig.hhs.gov/oei/reports/oei-02-17-00560.asp. OIG has also released versions of this code using R and SAS. For the code, see www.oig.hhs.gov/oei/reports/oei-02-17-00560.asp. This code calculates patients' average daily morphine equivalent dose (MED), which converts various prescription opioids and strengths into one standard value: equivalent milligrams of morphine. This measure is also called morphine milligram equivalent (MME). Users can use these data to identify patients at varying levels of risk. The code creates three patient-level tables: 1) OVERVIEW_MED table provides an overview of each patient's opioid utilization. 2) DAILY_MED table provides detailed daily MED amounts for selected patients. 3) AT_RISK_MED table identifies at-risk patients who had certain MED levels. /********************************************************************************** DATA PREPARATION In addition to reading the toolkit and taking the necessary steps to prepare the data, be sure to complete these two additional steps that are necessary for the SQL code: 1. Rename the prescription drug data file and fields 2. Create a calendar table 1. RENAME THE PRESCRIPTION DRUG DATA FILE AND FIELDS Before using this code, ensure that your prescription drug data and the code have the same file and field names. As needed, rename the prescription drug data so that the file and field names are as follows: FILE NAME: • DATA_CLEANED: Ensure that the file name of the cleaned prescription drug data is DATA_CLEANED. Note that the code does not exclude cancer and hospice patients. See the toolkit text for more information about excluding these patients from the analysis. FIELD NAMES: • PATIENT_ID: Unique patient identifier field name. Note: The analysis relies on each patient having a unique identifier. If the prescription drug data do not contain a unique identifier for each patient, additional steps should be taken to create one. • PRESCRIPTION_FILL_DATE: Prescription fill date field name. The field format needs to use a date format (e.g., YYYMMDD), which has each calendar date equal to 1 unit. This is required for the calculations to work properly. If the field uses another format (i.e., datetime or timestamp), some manipulation of the data may be needed to make it into a date format. • QUANTITY_DISPENSED: Quantity dispensed field name. • DAYS_SUPPLY: Days supply field name. • MME_CONVERSION_FACTOR: Morphine milligram equivalent (MME) conversion factor field name. • STRENGTH_PER_UNIT: Strength per unit field name. • PHARMACY_ID: Pharmacy unique identifier field name. Note: This field is optional. • PRESCRIBER_ID: Prescriber unique identifier field name. Note: This field is optional. 2. CREATE A CALENDAR TABLE The next step is to create a calendar table. The calendar table can be created on the user's data system or created in an external application (such as a spreadsheet) and then imported into the user's system. Before creating the table, first define the timeframe of the analysis. For example, a timeframe of the 2019 calendar year, or January 1, 2019 to December 31, 2019, has 365 calendar dates. Then take the following steps: • Create a new table named A_CALENDAR. • Create one field named CALENDAR_DATE. The field format needs to use a date format, which has each calendar date equal to 1 unit (e.g., YYYYMMDD). • Fill each record in the CALENDAR_DATE field with one calendar date starting with the first date of the timeframe and ending with the last date. For example, for a timeframe of 2019, CALENDAR_DATE will list each date from January 1, 2019 to December 31, 2019, and the table will have 365 records. /********************************************************************************** 1) OVERVIEW_MED: AN OVERVIEW OF EACH PATIENT'S OPIOID UTILIZATION This section of the code provides an overview of each patient's opioid utilization. The output table contains the following fields for each patient: PATIENT_ID Unique patient identifier. OPIOID_DAYS Days of opioid use, or the number of days during the timeframe when the patient has a daily MED that is greater than zero. ALL_AVG_MED Average daily MED for the entire study timeframe. For example, for a timeframe of 1 year, this is the patient's average daily MED for that year. MAX_AVG_MED_90 Highest average daily MED for any 90 days. This is the maximum average daily MED over any 90-day period in the timeframe. Brief overview of the code: First, the code cross joins A_CALENDAR to each patient ID. This step creates a table (called B_LEDGER) of calendar dates in the timeframe of the analysis for each patient. Second, the code calculates the daily MED and maps it to its calendar date for all of a patient's opioid prescriptions in a table called C_DAY_MED. Third, the code joins the daily MED values from C_DAY_MED onto B_LEDGER by patient ID and calendar date and sums the daily MED values for each date and creates a table called D_DAILY_MED. This table gives each patient's daily MED for each date in the timeframe. Fourth, the code creates a table called E_AVG_DAILY_MED when it inner joins D_DAILY_MED with itself to calculate the average daily MED for 90 days. The number of days in the period of MED analysis is set at 3 months, or 90 days. This number of days can be changed if needed. Last, the code calculates all the overview fields listed above for each patient and creates the table OVERVIEW_MED. */ create table b_ledger as select t1.PATIENT_ID, t2.calendar_date from (select distinct PATIENT_ID from DATA_CLEANED) t1 cross join a_calendar t2 create table c_day_med as select t1.PATIENT_ID, t2.calendar_date, (coalesce(t1.STRENGTH_PER_UNIT,0) * coalesce(t1.QUANTITY_DISPENSED,0) * coalesce(t1.MME_CONVERSION_FACTOR,0)) / case when t1.DAYS_SUPPLY = 0 then NULL /* Use appropriate missing value here. */ else t1.DAYS_SUPPLY end as day_med from DATA_CLEANED t1 inner join a_calendar t2 on t2.calendar_date between t1.PRESCRIPTION_FILL_DATE and t1.PRESCRIPTION_FILL_DATE + t1.DAYS_SUPPLY - 1 create table d_daily_med as select t1.PATIENT_ID, t1.calendar_date, sum(coalesce(t2.day_med,0)) as daily_med from b_ledger t1 left outer join c_day_med t2 on t1.PATIENT_ID = t2.PATIENT_ID and t1.calendar_date = t2.calendar_date group by t1.PATIENT_ID, t1.calendar_date create table e_avg_daily_med as select t1.PATIENT_ID, t1.calendar_date, t1.daily_med, sum(t2.daily_med)/90 as avg_med_90 from d_daily_med t1 inner join d_daily_med t2 on t1.PATIENT_ID = t2.PATIENT_ID and t2.calendar_date between t1.calendar_date /* This is set to a 90 day period. */ and t1.calendar_date + 89 /* This can be changed. */ group by t1.PATIENT_ID, t1.calendar_date, t1.daily_med order by t1.PATIENT_ID, t1.calendar_date create table overview_med as select PATIENT_ID, sum(case when daily_med > 0 then 1 else 0 end) as opioid_days, avg(daily_med) as all_avg_med, max(avg_med_90) as max_avg_med_90 from e_avg_daily_med group by PATIENT_ID /********************************************************************************** 2) DAILY_MED: DETAILED DAILY MED AMOUNTS FOR SELECTED PATIENTS This code provides the daily MED for each day in the study timeframe for selected patients. This code can be used to look more closely at the daily MED for selected patients with concerning patterns. For example, if a patient has an extremely high average daily MED, these data will show which days during the timeframe the patient received the highest amounts. When using this code, insert the patient ID(s) to output data for selected patient(s). */ create table daily_med as select PATIENT_ID, calendar_date, daily_med from e_avg_daily_med where PATIENT_ID = /* INSERT PATIENT ID HERE */ /********************************************************************************** 3) AT_RISK_MED: AT-RISK PATIENTS WHO HAD CERTAIN MED LEVELS This code identifies patients who, based on their average daily MED, are at risk of opioid misuse or abuse. The code identifies patients who met the criteria for the measures used in a series of OIG data briefs, including Opioid Use Decreased in Medicare Part D, While Medication-Assisted Treatment Increased (OEI-02-19-00390). This section of the code can be adjusted depending on each user's needs. For example, if the purpose of the analysis is to identify patients who may benefit from additional case management, the MED thresholds can be lowered. If the purpose is to refer incidences of possible drug diversion to law enforcement, the MED thresholds can be increased. Similarly, if the user's analysis is based on a shorter or longer timeframe than 1 year, the days of opioid use can be changed. The code identifies patients who appear to be doctor shopping. This analysis requires unique pharmacy identifiers and unique prescriber identifiers in the opioid prescription data. For each patient, it counts the number of distinct pharmacies and prescribers that had at least 1 opioid prescription with a fill date in the timeframe. As noted below, this section of the code is optional and should be skipped if these unique identifiers are not available. The output table contains the following fields in addition to the fields from the OVERVIEW_MED table: HIGH_MED Patients who received high amounts of opioids. Patients with an average daily MED greater than 120 mg for any 90-day period and had at least 90 days of opioid use. 1=Yes, 0=No. EXTREME_MED Patients who received extreme amounts of opioids. Patients with an average daily MED that was greater than 240 mg for the entire year and had at least 360 days of opioid use. 1=Yes, 0=No. DOCTORSHOP_MED Patients who appear to be doctor shopping. Patients with a high amount of opioids (i.e., average daily MED greater than 120 mg for any 90-day period) and who received opioid prescriptions from four or more prescribers and four or more pharmacies during the year. 1=Yes, 0=No. If unique pharmacy identifiers or unique patient identifiers are not available, do not run the PHARM_PRESC_COUNTS step and remove pharmacy and prescriber counts from AT_RISK_MED. */ create table pharm_presc_counts as select t1.PATIENT_ID, count(distinct t1.PHARMACY_ID) as pharmacy_count, count(distinct t1.PRESCRIBER_ID) as prescriber_count from DATA_CLEANED t1 inner join a_calendar t2 on t1.PRESCRIPTION_FILL_DATE = t2.calendar_date group by PATIENT_ID order by PATIENT_ID create table at_risk_med as select t1.PATIENT_ID, t1.opioid_days, t1.all_avg_med, t1.max_avg_med_90, coalesce(t2.pharmacy_count,0) as pharmacy_count, coalesce(t2.prescriber_count,0) as prescriber_count, case when t1.max_avg_med_90 > 120 and t1.opioid_days >= 90 then 1 else 0 end as high_med, case when t1.all_avg_med > 240 and t1.opioid_days >= 360 then 1 else 0 end as extreme_med, case when t1.max_avg_med_90 > 120 and t1.opioid_days >= 90 and t2.pharmacy_count >= 4 and t2.prescriber_count >= 4 then 1 else 0 end as doctorshop_med from overview_med t1 left join pharm_presc_counts t2 on t1.PATIENT_ID = t2.PATIENT_ID order by PATIENT_ID /* END OF CODE */ /********************************************************************************** Note that the Office of Inspector General (OIG) is providing this toolkit, including the associated programming code, to assist users in analyzing large datasets of prescription drug claims to identify individuals at risk of potential opioid abuse or misuse. This toolkit was prepared as a technical resource and is not intended to, and does not, create any rights, privileges, or benefits, substantive or procedural, enforceable by a party against the United States; its agencies or instrumentalities; its officers or employees; or any other person. The toolkit is provided in "as-is" condition, and OIG and its employees, agents, and staff disclaim any express or implied representation, warranty, or guarantee, including, but not limited to, the implied warranties of merchantability and fitness for a particular purpose. In particular, no representation is made that the information included in the toolkit, or any data the toolkit produces, is error free. The toolkit should not be used as the sole basis to determine whether an individual is abusing or overdosing on opioids or other prescription drugs, or in any determinations of coverage or dispensing by an insurer, pharmacy, provider, or other individual or organization. The toolkit is not intended to be used to determine compliance with any laws, regulations or other guidance. In no event shall OIG or its employees, agents, or staff be liable for any claim, damages, or liability, whether in an action of contract, tort or otherwise, and including direct, indirect, incidental, special, exemplary, or consequential damages, however caused, and on any theory of liability, arising in any way out of the use of this toolkit or its associated code, even if advised of the possibility of such damage. Compatibility of the toolkit with any user systems is not guaranteed, and any manipulation or alteration of the code is the sole responsibility of the user. */