Hi SAS community, I would appreciate your help in below problem: I have a scenario in which a data-set sorted by ID and begdate and Enddate. I have tried the code below to achieve my results and have used SAS 9.4 version: data want;
set have;
IF ID = lag(ID) AND DIFF = 1 or DIFF = 0 then prev = ENDDATE;
DIFF = BEGDATE- lag(ENDDATE)
lag_id = lag(id);
lag_begdate = lag(BEGDATE);
lag_enddate = lag(ENDDATE);
by id;
format BEGDATE date9. ENDDATE date9. prev date9. lag_enddate lag_begdate date9.;
run; Some IDs have multiple rows some have single rows with begdate and enddate. I want to check if begdate in row2 is equal to row1 enddate or if begdate in row2 = row1 endate +1. I basically want to see if the records can be clubbed to have a continuous period. For Example: If the row1 of ID #200 has an enddate - 2/20/2012 and row2 of ID #200 has begdate- 5/11/2012 then that row should stay the same in the output dataset. for id #200 Row 2 begdate was compared to row 1 enddate and difference is 81 days. NE 0 or 1 day - So this record should stay same for id #920 Row 2 begdate was compared to row 1 enddate and difference is 1 day EQ 0 or 1 day - so this should set my begdate for id #920 as row1 begdate and enddate as row2 enddate, thus indicating that it is a continuous period. Similarly, for id #920 Row 3 begdate was compared to row 2 enddate and difference is 1 day EQ 0 or 1 day.- so this should set my begdate for id #920 as row1 begdate and enddate as row3 enddate, thus indicating that it is a continuous period. Overall, I want the code to run through my data and loop by ID to compare and give me a record with a continuous period of the patient if there is no difference between consecutive records enddate and beginning date. Here is a sample of dataset and my expected results: ID begdate enddate 200 4/11/2011 2/20/2012 200 5/11/2012 10/31/2013 920 1/23/2003 1/11/2016 920 1/12/2016 3/29/2016 920 3/30/2016 7/21/2017 OUTPUT 200 4/11/2011 2/20/2012 200 5/11/2012 10/31/2013 920 1/23/2003 7/21/2017 Any help is appreciated. Thank you for your time and effort. Please feel free to comment or ask questions if my question seems unclear.
... View more
