Hi @PaigeMiller
I am creating the PATIENT_COHORT_W_RACE table so it doesn't exist yet. I realized that I didnt have the aliases for the tables they variable does exist in and added them. I get the same results. Here is the code that creates the work.patient_cohort table which works fine:
PROC SQL;
CREATE TABLE work.patient_cohort AS
SELECT DISTINCT a.mrn
, a.PatientNM
, a.dob
, a.payorNM
, scan(a.patientnm,1,',') as lastname
, scan(a.patientnm, 2, ',') as firstname
, a.SubscriberID as InsuredsIDNumber
, a.firstDt as cohort_entering_Date
, a.planID
, a.PayorID
,COMPRESS(SubscriberID, '', 'kd') AS EditedInsuredsIDNumber
FROM work.dataPull_2 a
INNER JOIN work.cntByMRN b
ON a.mrn= b.mrn
WHERE
/* payornm like '%HARVARD PILGRIM%' /*' reporting period*/
payorID = 110001
/*and *PayorID IN (170001);*TUFTS HEALTH PLAN*/
;QUIT;
Now I want to add the Race and the Ethnicity variable by creating the work.patient_cohort_w_Race table:
PROC SQL;
CREATE TABLE work.patient_cohort_w_Race AS
SELECT DISTINCT pc.*
,CASE WHEN r.PatientRaceDSC = 'ASIAN' THEN 'Asian'
WHEN r.PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
WHEN r.PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
WHEN r.PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
WHEN r.PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
WHEN r.PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
WHEN r.PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
WHEN r.PatientRaceDSC IS NULL THEN 'Unknown'
ELSE 'Other' END AS RACE
, CASE WHEN p.EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES'
ELSE 'NO' END AS HISPANIC_LATINO
FROM work.patient_cohort pc
LEFT JOIN pat.Identity I on pc.MRN = I.PatientIdentityID
LEFT JOIN pat.Race R on I.PatientIdentityID = R.PatientID
LEFT JOIN pat.Patient P on I.PatientIdentityID = P.PatientID
;quit;
