/*
Title: Part D Claim Level Adjudication Model
Author: Carter Gay
Date: 9/22/23
*/
/*
Step 1
Read PDE, Plan Type, Interval Transitions, Interval Benefits
*/
LIBNAME PDE ODBC NOPROMPT = '
Driver={SQL Server};
Server=TPA-SQL1;
Database=PDE_Claim_Model;
';
DATA Plan_Types;
SET PDE.Plan_Types;
RUN;
DATA Interval_Transitions;
SET PDE.Interval_Transitions;
RUN;
DATA Interval_Benefits;
SET PDE.Interval_Benefits;
RUN;
DATA PDEs;
SET PDE.Test_PDE;
RUN;
PROC FCMP OUTLIB = WORK.myfunx.pde;
SUBROUTINE DED(
/* INPUTS */
Total_GDC_Accumulator, Total_GDC_Accumulator_Next, Interval_End_Value,
/* OUTPUTS */
Interval_GDC, Patient_Pay
);
OUTARGS Interval_GDC, Patient_Pay;
IF Total_GDC_Accumulator >= Interval_End_Value THEN
Interval_GDC = 0.00;
ELSE Interval_GDC = MIN(Total_GDC_Accumulator_Next, Interval_End_Value) - Total_GDC_Accumulator;
Patient_Pay = Interval_GDC;
ENDSUB;
SUBROUTINE ICP(
/* INPUTS */
Total_GDC_Accumulator, GDC, Total_GDC_Accumulator_Next, Interval_End_Value,
CS_Type $, CS_Value,
/* OUTPUTS */
Interval_GDC, Patient_Pay
);
OUTARGS Interval_GDC, Patient_Pay;
IF Interval_End_Value < Total_GDC_Accumulator_Next
THEN Interval_GDC = Interval_End_Value - Total_GDC_Accumulator;
ELSE Interval_GDC = GDC;
IF CS_Type = 'Coinsurance' THEN
Patient_Pay = ROUND(Interval_GDC * CS_Value, 2);
ELSE IF CS_Type = 'Copay' THEN
Patient_Pay = MIN(Interval_GDC, CS_Value);
ENDSUB;
SUBROUTINE adjudicate(
/* INPUTS */
Year, Plan_ID, Interval_ID $, Interval_End_Field $, Interval_End_Value,
LI_Copay_Status, Applicable_Drug,
Scripts, GDC, Ingredient_Cost, Dispensing_Fee, Sales_Tax, Vaccine_Admin_Fee,
Total_GDC_Accumulator, Total_GDC_Accumulator_Next, TrOOP_Accumulator,
CS_Type $, CS_Value,
/* OUTPUTS */
Interval_GDC, Patient_Pay, LICS, PLRO, CPP, NPP, Reported_Gap_Discount
);
OUTARGS Interval_GDC, Patient_Pay, LICS, PLRO, CPP, NPP, Reported_Gap_Discount;
Interval_GDC = 0.00;
Patient_Pay = 0.00;
LICS = 0.00;
PLRO = 0.00;
CPP = 0.00;
NPP = 0.00;
Reported_Gap_Discount = 0.00;
IF Interval_End_Field = 'Total_GDC_Accumulator' AND Interval_End_Value > Total_GDC_Accumulator THEN DO;
IF Interval_ID = 'DED' THEN DO;
CALL DED(
Total_GDC_Accumulator, Total_GDC_Accumulator_Next, Interval_End_Value,
Interval_GDC, Patient_Pay
);
END;
IF Interval_ID = 'ICP' THEN DO;
CALL ICP(
Total_GDC_Accumulator, GDC, Total_GDC_Accumulator_Next, Interval_End_Value, CS_Type, CS_Value,
Interval_GDC, Patient_Pay
);
END;
END;
ENDSUB;
QUIT;
OPTIONS CMPLIB = WORK.myfunx;
PROC SQL;
CREATE TABLE PDE_Detail AS
SELECT DISTINCT
a.PDE_ID,
a.Contract,
a.PBP,
a.Member_ID,
a.Incurred_Year,
a.NDC,
a.Days_Supply,
a.Ingredient_Cost,
a.Dispensing_Fee,
a.Sales_Tax,
a.Vaccine_Admin_Fee,
a.GDC,
a.GDCB,
a.GDCA,
a.Patient_Pay,
a.Other_TrOOP,
a.LICS,
a.PLRO,
a.CPP,
a.NPP,
a.Reported_Gap_Discount,
a.Total_GDC_Accumulator,
a.Total_GDC_Accumulator_Next,
a.TrOOP_Accumulator,
a.Brand_Generic,
a.Beginning_Benefit_Phase,
a.Ending_Benefit_Phase,
a.Tier,
a.Pharmacy_Service_Type,
a.LI_Copay_Level_ID,
a.Applicable_Drug,
a.Daily_Cost_Share,
b.Interval,
b.Interval_ID,
c.Interval_End_Field,
c.Interval_End_Value,
d.CS_Type,
d.CS_Value
FROM PDEs a
INNER JOIN Plan_Types b
ON 'DS' = b.Plan_ID
AND b.Interval_ID IN ('DED', 'ICP')
INNER JOIN Interval_Transitions c
ON a.Incurred_Year = c.Year
AND b.Plan_ID = c.Plan_ID
AND b.Interval_ID = c.Interval_ID
AND a.LI_Copay_Level_ID = c.LI_Copay_Status
LEFT JOIN Interval_Benefits d
ON c.Year = d.Year
AND c.Plan_ID = d.Plan_ID
AND c.Interval_ID = d.Interval_ID
AND a.LI_Copay_Level_ID = d.LI_Copay_Status
AND a.Applicable_Drug = d.Applicable_Drug
ORDER BY a.PDE_ID, b.Interval;
QUIT;
PROC SQL;
CREATE TABLE NLI_PDE_Detail AS
SELECT
*
FROM PDE_Detail
WHERE LI_Copay_Level_ID <> 0;
QUIT;
DATA NLI_PDE_Detail;
SET NLI_PDE_Detail;
LI_Copay_Level_ID = 0;
RUN;
DATA PDE_Detail;
SET PDE_Detail;
IF Interval = 1 THEN
GDC_Allocated = 0;
ELSE GDC_Allocated = GDC_Allocated + Calc_Interval_GDC;
Total_GDC_Accumulator = Total_GDC_Accumulator + GDC_Allocated;
CALL adjudicate(
/* INPUTS */
Year, Plan_ID, Interval_ID, Interval_End_Field, Interval_End_Value,
LI_Copay_Level_ID, Applicable_Drug,
Scripts, GDC, Ingredient_Cost, Dispensing_Fee, Sales_Tax, Vaccine_Admin_Fee,
Total_GDC_Accumulator, Total_GDC_Accumulator_Next, TrOOP_Accumulator,
CS_Type, CS_Value,
/* OUTPUTS */
Calc_Interval_GDC, Calc_Patient_Pay, Calc_LICS, Calc_PLRO,
Calc_CPP, Calc_NPP, Calc_Reported_Gap_Discount
);
RETAIN Calc_Interval_GDC;
RUN;
DATA NLI_PDE_Detail;
SET NLI_PDE_Detail;
IF Interval = 1 THEN
GDC_Allocated = 0;
ELSE GDC_Allocated = GDC_Allocated + Calc_Interval_GDC;
Total_GDC_Accumulator = Total_GDC_Accumulator + GDC_Allocated;
CALL adjudicate(
/* INPUTS */
Year, Plan_ID, Interval_ID, Interval_End_Field, Interval_End_Value,
LI_Copay_Level_ID, Applicable_Drug,
Scripts, GDC, Ingredient_Cost, Dispensing_Fee, Sales_Tax, Vaccine_Admin_Fee,
Total_GDC_Accumulator, Total_GDC_Accumulator_Next, TrOOP_Accumulator,
CS_Type, CS_Value,
/* OUTPUTS */
Calc_Interval_GDC, Calc_Patient_Pay, Calc_LICS, Calc_PLRO,
Calc_CPP, Calc_NPP, Calc_Reported_Gap_Discount
);
RETAIN Calc_Interval_GDC;
RUN;
PROC SQL;
CREATE TABLE LI_Test_Summary AS
SELECT
PDE_ID,
SUM(Calc_Patient_Pay) AS Calc_Patient_Pay
FROM PDE_Detail
WHERE LI_Copay_Level_ID <> 0
GROUP BY PDE_ID;
QUIT;
PROC SQL;
CREATE TABLE NLI_Test_Summary AS
SELECT
PDE_ID,
SUM(Calc_Patient_Pay) AS Calc_Patient_Pay
FROM NLI_PDE_Detail
GROUP BY PDE_ID;
QUIT;
PROC SQL;
UPDATE a
SET
Calc_Patient_Pay = CASE WHEN c.Patient_Pay < a.Patient_Pay THEN c.Patient_Pay ELSE a.Patient_Pay END,
Calc_LICS = CASE WHEN c.Patient_Pay < a.Patient_Pay THEN 0 ELSE c.Patient_Pay - a.Patient_Pay END
FROM PDE_Detail a
INNER JOIN LI_Test_Summary b
ON a.PDE_ID = b.PDE_ID
INNER JOIN NLI_Test_Summary c
ON a.PDE_ID = b.PDE_ID
INNER JOIN NLI_PDE_Detail d
ON a.PDE_ID = d.PDE_ID
AND a.Interval_ID = d.Interval_ID
WHERE a.LI_Copay_Level_ID <> 0;
QUIT;
Here is what I have far. I have a table of prescription drug events. For each row in this table, the payment fields are calculated based on two accumulators (GDC and TROOP) that move members through the four phases (Deductible, ICP, Gap, and Catastrophic) as well as benefit parameters (these are sadly not included in the base data so we have to make a best guess in some cases). I would like to be able to produce a detailed breakdown of payments across all four phases (PDE_Detail has a row for each phase as well as relevant parameters). I think what I have now will work with enough testing; however, I hate the if statement used to call each phase function (only Deductible and ICP implemented so far). Really, I just want to know if there is a better approach in SAS
Click image to register for webinar
