@wheddingsjr
Try these statements and query, and see if helps you
OPTIONS DEBUG=DBMS_SELECT SASTRACE=',,,d' SASTRACELOC=SASLOG NOSTSUFFIX FULLSTIMER;
/* Calculate required date macros */
%let priorYrDt = %SYSFUNC(PUTN(%SYSFUNC(INTNX(year, "&begDt"d ,-1, same)),date9.));
%let priorDayDt = %SYSFUNC(PUTN(%SYSFUNC(INTNX(day, "&begDt"D ,-1, same)),date9.));
%put &=priorYrDt &=priorDayDt;
PROC SQL;
CREATE TABLE work.Datapull_epic AS
SELECT DISTINCT
H.PatientMRN as MRN
, H.HospitalAccountID as enc
, H.PrimaryPayorID as PayorID
, C.Subscriberid
, C.PlanID
/*
, DATEPART(H.DischargeDTS)
, MAX(H.DISCHARGEDTS) AS LAST_AGENT_DT
*/
, H.DISCHARGEDTS
, T.HospitalAccountClassDSC
/*
, PT.PatientNM */ /* Used PatientFirstNM and PatientLastNM from SharedLocal YM0214
*/
, PT.PatientFirstNM
, PT.PatientLastNM
, PT.BIRTHDTS
, P.PayorNM
, (T.hcpcs IN (&examCPT)) AS flag_exam /* any exam*/
, (T.hcpcs IN (&infuCPT) OR T.CPT IN (&infuCPT)) AS flag_infu /* infusion*/
, (T.hcpcs IN (&chemoCPT) OR T.CPT IN (&chemoCPT)) AS flag_chemo /* chemotherapy*/
, (T.hcpcs IN (&radOncCPT)) AS flag_radOnc
, (T.hcpcs IN ('C9027','J9271','C9453','J9299','C9284','J9228','C9399','C9483','J9022','C9491','J9023','C9492')) AS flag_immuno /* immunotherapy */
, CASE WHEN dischargedts BETWEEN "&priorYrDt"D AND "&priorDayDt"D THEN 0 /* Claims data for year prior to report start date */
WHEN dischargedts BETWEEN "&begDt"D AND "&endDt"D THEN 1
ELSE 2
END AS period
, SUM(CASE WHEN T.ChargeModifierListTXT LIKE '%DFP%' THEN 0 ELSE INT(T.TransactionCNT) END) AS units
, SUM(H.TotalChargeAMT) AS charges
FROM Fin.HospitalAccount H
LEFT JOIN Fin.Coverage C ON H.CoverageID = C.CoverageID
LEFT JOIN Ref.Payor P ON P.PayorID = C.PayorID
LEFT JOIN Fin.HospitalTransaction T ON H.HospitalAccountID = T.HospitalAccountID
/*INNER JOIN Pat.PATIENT PT ON PT.MRN=H.PATIENTMRN*/ /* YM0214 - changed to left join SharedLocal.Person.PatientExtension below */
LEFT JOIN Pext.PatientExtension PT ON H.PATIENTMRN = PT.DFCIMRN
WHERE
dischargedts BETWEEN "&priorYrDt"D AND "&endDt"D /* extract claims from 1 year prior to report start date until report end date */
AND H.PatientMRN IN
(SELECT DISTINCT A.PatientMRN
FROM Fin.HospitalAccount A
WHERE dischargedts BETWEEN "&begDt"D AND "&endDt"D
AND A.PatientMRN NE '') /* for mrn in cohort 1 period*/
AND T.HospitalAccountClassDSC = 'Outpatient' /* outpatient only */
AND ((T.hcpcs NE '') OR H.PatientMRN NE '')
GROUP BY 1,2,3,4,5,6,7,8,9,10,11,12,13,14,15
ORDER BY H.PatientMRN, period DESC;
QUIT;
Good luck,
Ahmed
... View more
