SAS Programming

DATA Step, Macro, Functions and more
BookmarkSubscribeRSS Feed
kbinan
Obsidian | Level 7

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)
1 REPLY 1
s_lassen
Meteorite | Level 14

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.

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 441 views
  • 0 likes
  • 2 in conversation