Tip: Deduping Pharmacy Enrollment Data and Claims from PBMs
- Kenley Money
Number | 0161 |
|---|---|
Submitting Entity (SE) | All |
Add Date | Aug 21, 2024 |
Update Date |
|
Update | Included in the Sept 2024 data tips email |
Description | Claim counts for overlapping carriers and PBMs: The Arkansas APCD often receives enrollment data and pharmacy claims from both the primary submitter or insurer, e.g. Centene (80799), and the associated Pharmacy Benefits Manger (PBM), e.g. CareMark (99CAR1). Records are sometimes provided by both entities for the same prescription fill. Selecting Enrollment Records: Because it is not always easy to know which submitter uses a PBM, it is recommended that preparatory discovery be done first using the enrollment data:
-- Step 1: Create subset containing PBM enrollment data for matching
SELECT ME001 as PBM_ME001, ME998 as PBM_ME998, ME013 as PBM_ME013, ME014 as PBM_ME014, ME162A as PBM_ME162A
into #tmp_PBM_Members
FROM (
SELECT A.ME001, A.ME998, A.ME013, A.ME014, A.ME162A, A.ME107
FROM [database_name].[dbo].[Member] A
JOIN
(SELECT ME998, ME013, ME014, ME162A, COUNT(DISTINCT ME001) AS cnt
FROM [database_name].[dbo].[Member]
GROUP BY ME998, ME013, ME014, ME162A
HAVING COUNT(DISTINCT ME001) > 1) b
ON A.ME998 = B.ME998 AND A.ME013 = B.ME013 AND A.ME014 = B.ME014
GROUP BY A.ME001, A.ME998, A.ME013, A.ME014, A.ME162A, A.ME107
) z
WHERE z.me001 in ('99SSC1','99CAR1','99MDI1','99MDI2','99MDI3','99MDI4','99MDI5',
'99MAG1','99NAV1','99ESA1')
GROUP BY ME001, ME998, ME013, ME014, ME162A
--3761114
-- Step 2: Join PBM subset to enrollment data to create Non-PBM/PBM pairs with matching SIDs, Enrollment Dates
SELECT *
into #tmp_PBM_to_Carrier_linkage
FROM #tmp_PBM_Members a JOIN (
SELECT A.ME001, A.ME998, A.ME013, A.ME014, A.ME162A, A.ME107
FROM [database_name].[dbo].[Member] A
JOIN
(SELECT ME998, ME013, ME014, ME162A, COUNT(DISTINCT ME001) AS cnt
FROM [database_name].[dbo].[Member]
GROUP BY ME998, ME013, ME014, ME162A
HAVING COUNT(DISTINCT ME001) > 1) b
ON A.ME998 = B.ME998 AND A.ME013 = B.ME013 AND A.ME014 = B.ME014
GROUP BY A.ME001, A.ME998, A.ME013, A.ME014, A.ME162A, A.ME107
--order by a.ME998, a.ME013, a.ME014, a.ME162A)
) z
on a.PBM_ME998=z.ME998 and a.PBM_ME013 = z.ME013 and a.PBM_ME014 = z.ME014 and a.PBM_ME162A = z.ME162A
where z.ME001 not in ('99SSC1','99CAR1','99MDI1','99MDI2','99MDI3',
'99MDI4','99MDI5','99MAG1','99NAV1','99ESA1')Submitting entity codes for PBMs:
When enrollment records from insurers are paired with one of the PBMs groupings should be evaluated to determine which to use to address the study question - the PBM enrollment record or the TPA/carrier enrollment record. It is possible to keep both when determining which pharmacy claims to select. Selecting Pharmacy Claims: Group pharmacy claims on NDC drug code (PC026), pharmacy number (PC018), prescription fill date (PC032) and prescription number (PC058). When the submitter IDs (PC001) are different and one includes a PBM Once PBM claims have been grouped with claims from the insurer, examine the claims data to understand how to combine them. Sometimes, the data is exactly the same while other times the PBM claims has data the insurer’s claim does not and visa versa. Similar code can be used to find the insurer and PBM claims pairings. -- Step 1: Create subset containing PBM claims data for matching
SELECT PC001 as PBM_PC001, PC026 as PBM_PC026, PC018 as PBM_PC018, PC032 as PBM_PC032, PC058 as PBM_PC058
into #tmp_PBM_Phm_Claims --DROP TABLE #tmp_PBM_Phm_Claims
FROM (
SELECT A.PC001, A.PC107, A.PC026, A.PC018, A.PC032, A.PC058
FROM [database_name].[dbo].[Pharmacy] A
JOIN
(SELECT PC107, PC026, PC018, PC032, PC058, COUNT(DISTINCT PC001) AS cnt
FROM [database_name].[dbo].[Pharmacy]
GROUP BY PC107, PC026, PC018, PC032, PC058
HAVING COUNT(DISTINCT PC001) > 1) b
ON A.PC026 = B.PC026 AND A.PC018 = B.PC018 AND A.PC032 = B.PC032 AND A.PC058 = B.PC058
GROUP BY A.PC001, A.PC107,A.PC026, A.PC018, A.PC032, A.PC058
) z
WHERE z.PC001 in ('99SSC1','99CAR1','99MDI1','99MDI2','99MDI3','99MDI4','99MDI5',
'99MAG1','99NAV1','99ESA1')
GROUP BY PC001, PC001, PC026, PC018, PC032, PC058
-- Step 2: Join PBM subset to claims data to create Non-PBM/PBM pairs with matching drug code (PC026),
-- fill date (PC032), pharmcy number (PC018) and script number (PC058)
SELECT *
into #tmp_PBM_to_Carrier_linkage_CLM -- drop table #tmp_PBM_to_Carrier_linkage_CLM
FROM #tmp_PBM_Phm_Claims a JOIN (
SELECT A.PC001, A.PC107, A.PC026, A.PC018, A.PC032, A.PC058
FROM [database_name].[dbo].[Pharmacy] A
JOIN
(SELECT PC026, PC018, PC032,PC058, COUNT(DISTINCT PC001) AS cnt
FROM [database_name].[dbo].[Pharmacy]
GROUP BY PC026, PC018, PC032,PC058
HAVING COUNT(DISTINCT PC001) > 1) b
ON A.PC026 = B.PC026 AND A.PC018 = B.PC018 AND A.PC032 = B.PC032 AND A.PC058 = B.PC058
GROUP BY A.PC001, A.PC107, A.PC026, A.PC018, A.PC032, A.PC058
) z
on a.PBM_PC026=z.PC026 and a.PBM_PC018 = z.PC018 and a.PBM_PC032 = z.PC032 and a.PBM_PC058 = z.PC058
where z.PC001 not in ('99SSC1','99CAR1','99MDI1','99MDI2','99MDI3',
'99MDI4','99MDI5','99MAG1','99NAV1','99ESA1')
|
Active/Resolved | Active |
Action / Resolution |
|
Category 1 | PBM |
Category 2 | Hash ID |
Tip / Issue | Tip |