Hello all
I ran this query and got my desired dataset:
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
payorID = 110001
;QUIT;
What I want to do now is use the MRN variable that was created in the above code and use them to run the below query so that only the MRN's from the top query is used in the second query:
PROC SQL;
CREATE TABLE work.patient_race AS
SELECT DISTINCT
I.PatientIdentityID as PatientMRN
,py.PayorID
,py.PayorNM
,Loc.RevenueLocationNM
,CASE WHEN PatientRaceDSC = 'ASIAN' THEN 'Asian'
WHEN PatientRaceDSC = 'BLACK OR AFRICAN AMERICAN' THEN 'Black or African American'
WHEN PatientRaceDSC = 'Hispanic or Latino' THEN 'Hispanic or Latino'
WHEN PatientRaceDSC LIKE ('WHITE%') THEN 'White or Caucasian'
WHEN PatientRaceDSC = 'American Indian or Alaska Native' THEN 'American Indian or Alaska Native'
WHEN PatientRaceDSC = 'Native Hawaiian or Other Pacific Islander' THEN 'Native Hawaiian or Other Pacific Islander'
WHEN PatientRaceDSC IN ('Declined', 'Unavailable') THEN 'Unknown'
WHEN PatientRaceDSC IS NULL THEN 'Unknown'
ELSE 'Other' END AS RACE
, CASE WHEN EthnicGroupDSC in ('Yes Hispanic','Hispanic') THEN 'YES'
ELSE 'NO' END AS HISPANIC_OR_LATINO
FROM fin.HospitalAccount HA
LEFT JOIN ref.Location LOC ON HA.DischargeEpicLocationID = LOC.LocationID
LEFT JOIN pat.Race R on HA.PatientID = R.PatientID
LEFT JOIN pat.Patient P on HA.PatientID = P.PatientID
LEFT JOIN fin.HospitalTransaction HT on HA.HospitalAccountID = HT.HospitalAccountID
LEFT JOIN pat.Identity I on HA.PatientID = I.PatientID
LEFT JOIN ref.Payor PY ON HA.FinancialClassCD = PY.FinancialClassCD
;quit;
Is it possible to do that?
