How to I retain only the first instance of a join?

kbinan — Thu, 30 Apr 2020 08:13:40 GMT

I'm not even sure how to ask this question exactly! I have two datasets of claims data , one for Inpatient Hospital Stays with a unique identifier Event ID, Admit Date and Discharge Dates. The 2nd dataset is all the claim detail. I'm trying to group the claim detail into the Unique Identifier - simple enough with a join on a Member ID and date ranges (below). Which I originally did, however, I have discovered scenarios where Member could have back-to-back IP stays with the Discharge Date of the 1st stay equal to the Admit Date of the 2nd - stopping and starting on the same day (transferred hospitals) and a single claim that has a data of service that same day so it currently "joins" to BOTH IP stays - and I only want it to join to the first. Is there a way to even do that?

Thank you in advance for any advice!

FROM WORK.CLM_DATA2 A

INNER JOIN WORK.CONF_DATA_FINAL_&QTR. CF 
 		ON A.MBR_SYS_ID = CF.MBR_SYS_ID
	   AND (A.FST_SRVC_DT ge CF.ADMIT_DT	
	    and A.FST_SRVC_DT LE CF.DISCHG_DT)

Re: How to I retain only the first instance of a join?

s_lassen — Thu, 30 Apr 2020 08:55:46 GMT

One way is to use GROUP BY and HAVING, something like

FROM WORK.CLM_DATA2 A

INNER JOIN WORK.CONF_DATA_FINAL_&QTR. CF 
 		ON A.MBR_SYS_ID = CF.MBR_SYS_ID
	   AND (A.FST_SRVC_DT ge CF.ADMIT_DT	
	    and A.FST_SRVC_DT LE CF.DISCHG_DT)
group by A.MBR_SYS_ID
having CF.ADMIT_DT = min(CF.ADMIT_DT)

You may want to add more CF variables to the GROUP BY clause, so that you get all the different claims.

It is not possible to give more specific advice, when there is no example of the data (post as a data step) and no SELECT clause shown.

topic Re: How to I retain only the first instance of a join? in SAS Programming

How to I retain only the first instance of a join?

Re: How to I retain only the first instance of a join?