Running a query from another query

[wheddingsjr]

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?

[Reeza]

Add in via WHERE or another join. 

where i.patientID in  (select distinct mrn from patient_cohort) 

---

@wheddingsjr wrote:

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?

---

[ChrisNZ]

A join is usually faster than a    in (select from ... )  clause 

[Reeza]

Yup but not sure of the structure of the other tables and if they have multiple IDs and then it would likely be a right/inner join instead and no way to test it. The solution posted will work and is easy to understand.

[ChrisNZ]

I'd use 

  join (select unique .... from TABLE2) 

but that's me.