Below is a snap shot of the table, obviously it’s bigger than this, but these are the fields I need to use. The issue is that lines 3 and 4 are all 1 inpatient stay, with just an interim bill submitted with a 1 day "gap". Lines 1, 2, 5 are all discrete IP stays. I need to group lines 3 and 4 and assign 06/15/2017 as the IP_START_DATE to both so that a “visit”: would be (123456||06/15/2017||987654) for all lines 3 and 4. The other part of this is that it is not always just two lines, it could be 3 or more that all combine to a > 90 day stay with interim bills so the lag function doesn't seem to be working for me. Index Member ID DOS Start DOS End Provider ID OBS FK_PTY_ID MEDI_START_DATE MEDI_END_DATE HOSP_CODE 1 12345 02/01/2017 02/01/2017 987654 2 12345 03/01/2017 03/10/2017 987654 3 12345 06/15/2017 06/30/2017 987654 4 12345 07/01/2017 07/10/2017 987654 5 12345 08/20/2017 08/24/2017 987654 My idea was to make a “sub_visit” of (FK_PTY_ID||HOSP_CODE), sort by that, then list all the MEDI_START_DATE by ascending and get the “gap” from MEDI_END_DATE to MEDI_START_DATE2 (lagged). If that “gap” is <= 1 then consider it all one visit. If not, it’s a new visit. Something like this: Index Member ID DOS Start DOS End Provider ID Created Created OBS FK_PTY_ID MEDI_START_DATE MEDI_END_DATE HOSP_CODE SUB_VISIT GAP 1 12345 02/01/2017 02/01/2017 987654 12345987654 . 2 12345 03/01/2017 03/10/2017 987654 12345987654 30 3 12345 06/15/2017 06/30/2017 987654 12345987654 97 4 12345 07/01/2017 07/10/2017 987654 12345987654 1 5 12345 08/20/2017 08/24/2017 987654 12345987654 41 Then, reading back it will look for a gap <= to 1 and assign the first MEDI_START_DATE within that visit only. (without reading all the way back to line 1, b/c is was the same member at the same provider but a very different visit months before.) Index Member ID DOS Start DOS End Provider ID Created Created Created OBS FK_PTY_ID MEDI_START_DATE MEDI_END_DATE HOSP_CODE SUB_VISIT GAP IP_START 1 12345 02/01/2017 02/01/2017 987654 12345987654 . 02/01/2017 2 12345 03/01/2017 03/10/2017 987654 12345987654 30 03/01/2017 3 12345 06/15/2017 06/30/2017 987654 12345987654 97 06/15/2017 4 12345 07/01/2017 07/10/2017 987654 12345987654 1 06/15/2015 5 12345 08/20/2017 08/24/2017 987654 12345987654 41 08/20/2017 I had been trying to use code like this, but it will pnly look back one line. PROC SORT DATA=ALLORES.SUB_VISIT OUT=ALLORES.SUB_VISIT; BY SUB_VISIT; BY MEDI_START_DATE; RUN; DATA SUB_VISIT; SET WORK.SUB_VISIT; MEDI_S1= MEDI_START_DATE; MEDI_S2 = LAG(MEDI_START_DATE); MEDI_E1 = MEDI_END_DATE; MEDI_E2 = LAG(MEDI_END_DATE); GAP = MEDI_E1 - MEDI_S2; IF FK_PTY_ID = LAG(FK_PTY_ID) AND HOSP_CODE = LAG(HOSP_CODE) AND GAP <= 1 THEN IP_START_DATE = put(MEDI_START_DATE,yymmdd10.); ELSE IP_START_DATE = put(lag(MEDI_START_DATE),yymmdd10.); RUN; Thanks in adance!
... View more