Good afternoon! I am trying to ultimately create a single record to represent an Inpatient Hospital stay from detailed claims data which has an Admit and Discharge date that spans the entirety each event.  I created Conf_Claim which tells me this is the same person and hospital all submitted on a single claim.  I also created Conf_Prov which tells me it’s the same person and hospital, regardless of claim since there can be multiple. First Pass to create IP_Start and IP_End worked as expected: proc sort data=work.CONF_DATA1; by CONF_CLM FST_SRVC_DT; run; DATA WORK.conf_data2; do _n_=1 by 1 until(last.CONF_CLM); SET WORK.CONF_DATA1; BY CONF_CLM; IP_START = min(IP_START, FST_SRVC_DT); IP_END = max(IP_END,LST_SRVC_DT); end; do _n_=1 to _n_; set WORK.CONF_DATA1; output; end; format IP_START IP_END date9.; run;   In some cases, all records come in on a single claim (CONF_CLM) so min/max is easy, as in the first 3 examples, so the above code works.  However, in other cases it is represented by multiple claims, or where I have the same member and hospital but the date are not continuous (meaning 2 or more separate events) it gets more complicated. I had tried something similar to above, but it grouped those instances where the date was not continuous into one event – I don’t want that to happen. proc sort data=work.CONF_DATA2; by CONF_CLM CONF_PROV FST_SRVC_DT; run; DATA WORK.conf_data3; do _n_=1 by 1 until(last.CONF_PROV);  SET WORK.CONF_DATA2;    BY CONF_PROV;   ADMIT =min(ADMIT,IP_START);   DISCHG=max(DISCHG,IP_END);  end;  do _n_=1 to _n_;   set WORK.CONF_DATA2;   output;  end;  format  ADMIT DISCHG date9.; run;   Example of current output:   CONF_CLM CONF_PROV Member FST_SRVC_DT LST_SRVC_DT PROV_NAME IP_START IP_END ADMIT DISCHG ACTUAL_ADMIT ACTUAL_DISCHG 26443576211258724_752167878 264435762611258724 Mbr #1 01Dec2018 01Dec2018 DOCTORSHOSPITALOFSARASOTA 01Dec2018 07Dec2018 01Dec2018 07Dec2018 01Dec2018 07Dec2018 264435762611258724_752167878 264435762611258724  Mbr #1 02Dec2018 03Dec2018 DOCTORSHOSPITALOFSARASOTA 01Dec2018 07Dec2018 01Dec2018 07Dec2018 01Dec2018 07Dec2018 264435762611258724_752167878 264435762611258724  Mbr #1 04Dec2018 07Dec2018 DOCTORSHOSPITALOFSARASOTA 01Dec2018 07Dec2018 01Dec2018 07Dec2018 01Dec2018 07Dec2018 264437408621113740_751297161 264437408621113740  Mbr#2 02Dec2018 02Dec2018 OAK HILL HOSPITAL 02Dec2018 04Dec2018 02Dec2018 04Dec2018 02Dec2018 04Dec2018 264437408621113740_751297161 264437408621113740  Mbr#2 03Dec2018 04Dec2018 OAK HILL HOSPITAL 02Dec2018 04Dec2018 02Dec2018 04Dec2018 02Dec2018 04Dec2018 264437539621113740_752433592 264437539621113740  Mbr#3 06Dec2018 07Dec2018 ST LUCIE MEDICAL CENTER 06Dec2018 09Dec2018 06Dec2018 09Dec2018 06Dec2018 09Dec2018 264437539621113740_752433592 264437539621113740  Mbr#3 08Dec2018 09Dec2018 ST LUCIE MEDICAL CENTER 06Dec2018 09Dec2018 06Dec2018 09Dec2018 06Dec2018 09Dec2018 264439412621694180_751692097 264439412621694180  Mbr#4 03Dec2018 04Dec2018 JFK MEDICAL CENTER 03Dec2018 04Dec2018 03Dec2018 27Dec2018 03Dec2018 04Dec2018 264439412621694180_755242526 264439412621694180  Mbr#4 24Dec2018 27Dec2018 JFK MEDICAL CENTER 24Dec2018 27Dec2018 03Dec2018 27Dec2018 24Dec2018 27Dec2018 264442542621113740_753831921 264442542621113740  Mbr#5 14Dec2018 16Dec2018 ST LUCIE MEDICAL CENTER 14Dec2018 16Dec2018 14Dec2018 24Dec2018 14Dec2018 24Dec2018 264442542621113740_754854457 264442542621113740  Mbr#5 17Dec2018 18Dec2018 ST LUCIE MEDICAL CENTER 17Dec2018 18Dec2018 14Dec2018 24Dec2018 14Dec2018 24Dec2018 264442542621113740_754976954 264442542621113740  Mbr#5 18Dec2018 24Dec2018 ST LUCIE MEDICAL CENTER 18Dec2018 24Dec2018 14Dec2018 24Dec2018 14Dec2018 24Dec2018 264442954621113740_753825785 264442954621113740  Mbr#6 14Dec2018 16Dec2018 ST LUCIE MEDICAL CENTER 14Dec2018 16Dec2018 14Dec2018 30Dec2018 14Dec2018 16Dec2018 264442954621113740_757277841 264442954621113740  Mbr#6 28Dec2018 30Dec2018 ST LUCIE MEDICAL CENTER 28Dec2018 30Dec2018 14Dec2018 30Dec2018 28Dec2018 30Dec2018 26445321621694180_755242593 264453210621694180  Mbr #7 24Dec2018 25Dec2018 JFK MEDICAL CENTER 24Dec2018 25Dec2018 24Dec2018 11Jan2019 24Dec2018 25Dec2018 264453210621694180_758430254 264453210621694180  Mbr #7 04Jan2019 09Jan2019 JFK MEDICAL CENTER 04Jan2019 11Jan2019 24Dec2018 11Jan2019 04Jan2019 11Jan2019   How can I make my process use the LST_SRVC_DT of a current record compared to the FST_SRVC_DT of the next records and if it is  <= 1 then treat it a part of the same event and correctly create what I actually want as shown in the ACTUAL_ADMIT and ACTUAL_DISCHG fields, otherwise treat it as a new event so I can ultimately roll this all up to one record per event? Thank you! ... View more

Hello SAS users! I am trying to get a MIN and MAX date onto my data to enable the  creation of  a single record when I roll this up by CLM_NUM.  What I want is the earliest FST_SRVC_DT and the latest LST_SRVC_DT on all claim lines for a given CLM_NUM.   This is what I want it to look like:   CLM_NUM FST_SRVC_DT LST_SRVC_DT IP_START IP_END 756653xxXXX 22Dec2018 24Dec2018 12/22/2018 12/31/2018 756653xxXXX 25Dec2018 29Dec2018 12/22/2018 12/31/2018 756653xxXXX 30Dec2018 31Dec2018 12/22/2018 12/31/2018   Data is previously sorted by CLM_NUM - what am I doing wrong?  I end up with only the last record looking like I want it to.   DATA WORK.want; SET WORK.have; BY CLM_NUM; IF FIRST.CLM_NUM THEN DO;   IP_START = FST_SRVC_DT; END;   ELSE IF LAST.CLM_NUM THEN DO;    IP_END = LST_SRVC_DT; END; RETAIN IP_START IP_END; format IP_START MMDDYY10.; format IP_END MMDDYY10.;   IP_START = MIN(IP_START,FST_SRVC_DT); IP_END = MAX(IP_END,LST_SRVC_DT); RUN;   ... View more