start and stop dates for earlier timepoint

[monday89]

I have the following dataset

WHat I want to do is PER Patient PER DAY, if two stop date/times are the same, then I would like to make the second date/time as the new stop date time for the previously record BUT second start and stop date remain the same. If there are no same stop dates, then keep the original

patientid	method	start_date	stop_date	weight	day
1	2	1/2/19 11:20	1/2/19 20:12	21	1
1	3	1/2/19 18:15	1/2/19 20:12	10	1
1	2	1/3/19 1:00	1/3/19 21:01	10	2
2	2	1/4/19 9:12	1/4/19 14:01	12	1
2	2	1/5/19 9:16	1/5/19 14:01	12	1

Something like this:

patientid	method	start_date	stop_date	weight	day	new_start_date	new_stop_date
1	2	1/2/19 11:20	1/2/19 20:12	21	1	1/2/19 11:20	1/2/19 18:15
1	3	1/2/19 18:15	1/2/19 20:12	10	1	1/2/19 18:15	1/2/19 20:12
1	2	1/3/19 1:00	1/3/19 21:01	10	2	1/3/19 1:00	1/3/19 21:01
2	2	1/4/19 9:12	1/4/19 14:01	12	1	1/4/19 9:12	1/4/19 14:01
2	2	1/5/19 9:16	1/5/19 14:01	12	1	1/5/19 9:16	1/5/19 14:01

I have no idea how to start this! 😞

[Patrick]

Thanks for posting sample data and the desired result.

Going forward ideally post sample data in the form of a SAS datastep as done in below code. This gives us the opportunity to spend the time on providing a solution to you instead of preparing the source data.

%let sv_datestyle=%sysfunc(getoption(datestyle,keyexpand));
options datestyle=mdy;
data have;
  infile datalines dsd truncover;
  input 
    patientid $ method $ 
    start_date:anydtdtm. stop_date:anydtdtm. 
    weight day;
  format start_date stop_date datetime20.;
  datalines;
1,2,1/2/19 11:20,1/2/19 20:12,21,1
1,3,1/2/19 18:15,1/2/19 20:12,10,1
1,2,1/3/19 1:00,1/3/19 21:01,10,2
2,2,1/4/19 9:12,1/4/19 14:01,12,1
2,2,1/5/19 9:16,1/5/19 14:01,12,1
;
options &sv_datestyle;

proc sort data=have;
  by patientid start_date stop_date;
run;

data want(drop=_:);
  merge have
        have(firstobs=2
             keep=patientid start_date 
             rename=(patientid=_id start_date=_start)
            );

  format new_start_date new_stop_date datetime20.;
  new_start_date=start_date;
  new_stop_date=stop_date;

  if patientid=_id and stop_date>_start then 
    new_stop_date=_start;
run; 

proc print data=want;
run;

[ed_sas_member]

Hi @monday89 

Here is an approach to do this:

data have;
	infile datalines dlm="09"x;
	input patientid method start_date:anydtdtm. stop_date:anydtdtm. weight day;
	format start_date stop_date datetime20.;
	datalines;
1	2	1/2/19 11:20	1/2/19 20:12	21	1
1	3	1/2/19 18:15	1/2/19 20:12	10	1
1	2	1/3/19 1:00	1/3/19 21:01	10	2
2	2	1/4/19 9:12	1/4/19 14:01	12	1
2	2	1/5/19 9:16	1/5/19 14:01	12	1
;
run;

proc sort data=have out=have_sorted;
	by patientid start_date;
run;

data have2;
	merge have_sorted
		  have_sorted(keep=patientid start_date firstobs=2 
					  rename=(patientid=_patientid start_date=_start_date));

	format new_stop_date datetime20.;
	if patientid=_patientid and _start_date < stop_date then new_stop_date=_start_date;
	else new_stop_date=stop_date;

	rename start_date=new_start_date;
	drop stop_date _:;
run;