Hi @Bobo3
I don't understand why do you still expect overlapping dates in your output table:
e.g. for patient 001:
001 40001 1/3/2018 1/6/2018 001 1/6/2018 1/14/2018 -> why don't you expect 1/7/2018 and 1/13/2018 ? 001 40001 1/14/2018 1/19/2018 001 1/19/2018 3/7/2018 -> why don't you expect 1/20/2018 and 3/6/2018 ? 001 40001 3/7/2018 3/10/2018 001 3/10/2018 12/31/2018 -> why don't you expect 3/11/2018 and 12/31/2018 ?
If this is a mistake, you could try the following program. When FacID is missing, it corresponds to date ranges without hospitalisations.
Hope this help.
data have;
input PatID $ FacID $ clm_from_dt:mmddyy8. clm_thru_dt:mmddyy8.;
format clm_from_dt clm_thru_dt date9.;
cards;
001 40001 1/3/2018 1/6/2018
001 40001 1/14/2018 1/19/2018
001 40001 3/7/2018 3/10/2018
002 43517 2/1/2018 3/31/2018
002 40002 2/10/2018 2/11/2018
002 42168 2/12/2018 2/22/2018
002 43517 4/1/2018 5/31/2018
002 40002 4/5/2018 4/7/2018
002 43517 6/1/2018 7/31/2018
;
run;
/* STEP1 : creation of 1 dataset with 1 row per day of hospitalization */
data day_hosp;
set have;
/* Each hospital transition is identified by the variable source */
source + 1;
/* 1 row per day at hospital */
format day date9.;
day=clm_from_dt - 1;
do i=clm_from_dt to clm_thru_dt;
day + 1;
output;
end;
drop i clm_from_dt clm_thru_dt;
run;
proc sort data=day_hosp;
by patID day;
run;
data day_hosp2;
set day_hosp;
by patID day source;
/* remove overlapping days*/
if last.day then
output;
run;
/* STEP2 : creation of a dataset with 1 row per day until 12/31/2018 */
proc sql;
create table day_all as select patID, min(clm_from_dt) as start_date
format=date9., '31DEC2018'd as end_date format=date9.
from have group by patID order by patID;
quit;
data day_all2;
set day_all;
format day date9.;
by patID;
do day=(start_date) to (end_date);
day+1;
day=day-1;
output;
end;
drop start_date end_date;
run;
/* STEP3 : merge */
data day_all;
format day date9.;
merge day_hosp2 day_all2;
by patID day;
run;
data want;
set day_all;
format clm_from_dt clm_thru_dt date9.;
by patID source notsorted;
retain clm_from_dt;
if first.source then
clm_from_dt=day;
if last.source then
clm_thru_dt=day;
if last.source then
output;
keep PatID FacID clm_from_dt clm_thru_dt;
run;
proc print data=want;
id patID;
run;
... View more
