Identify procedure dates from diagnosis dates

[CathyVI]

Hi,

Sorry this is a long text, am just trying to be well detailed.

I am trying to determine two  dates from a dataset. 

1. BIOPSY date which is define as the closest date to but pre DIAGNOSIS date

2. MRI date which is define as the closest to but pre BIOPSY date. 

 I was able to generate the BIOPSY date but I am struggling to get the MRI date. I have variable BIOP, MRI & MRI_BIOP. These are variables I have generated when BIOP=1 or MRI=1 or MRI_BIOP=1. 

data have;
input pt_id $ Pdate:mmddyy10.  month_diag year_diag BIOP MRI MRI_BIOP ;
format date mmddyy10.;
datalines;
A 09/01/2016  04 2017 0 1 0
A 01/01/2017  04 2017 0 1 0
A       .             04 2017 1 0 0
A 03/25/2017  04 2017 1 0 0
B 09/02/2007  08 2007 1 0 0
B 06/15/2007  08 2007 0 1 0

B 06/13/2007  08 2007 0 1 0
B 07/28/2007  08 2007 1 0 0

;
run;

proc sort data = have; by pt_ID Pdate ;proc print; run;
data want ;
set have ;
by pt_id date ;

/*Make a diagnosis date that is the 15thday of the month of diagnosis */

DiagDate = mdy(month_diag,15,year_diag) ;

retain dt_biop dt_mri ;

if first.pt_id then do ;
call missing(dt_biop,dt_mri) ;
end ;
if Pdate = . then dt_biop=. ;
else if Pdate <= DiagDate & biop=1 then dt_biop=Pdate ;
if mri=1 & Pdate <= dt_biop then dt_mri=Pdate ;

if last.pt_id ;

format DiagDate dt_biop dt_mri date9. ;
run ;

OUTPUT I WANTED

For example in Pt_id A, 03/25/2017(procedure date) is the closet date/pre to 08/15/2007(diagnosis date) when BIOP=1 so this is the BIOPSY date. The next date closet & pre to 03/25/2017 is 01/01/2017 when MRI=1, so this is suppose to be the MRI date.

In Pt_id B, 07/28/2007 is the closet & pre to 08/15/2007when BIOP=1 so this should be BIOPSY date then 06/15/2007 is the closet &pre 07/28/2007 when MRI=1 so this should be the MRI date. Any help will be very appreciated. Thanks

[ballardw]

I suggest providing an actual meaning to us of the variables BIOP MRI MRI_BIOP.

If you only have month and year of diagnosis (why???) I would not assume a date of the 15th of the month for the diagnosis since you are looking for dates before and close. What if the date from biopsy is only a few days and the biopsy was after the 15th of the month of the diagnosis?

What, in terms of your problem, does this record contribute? No date at all. Should it be considered for any purpose? If so, what?

A       .             04 2017 1 0 0

Your really have to explain this:

For example in Pt_id A, 03/25/2017(procedure date) is the closet date/pre to 08/15/2007(diagnosis date)

The date of 8/15/2007 you are assuming for a diagnosis is a completely different Pt_id: B.  So why are you comparing anything related to Pt_id=A??? Not only that but 03/25/2017 is not "pre" 08/15/2007 in any case.

You should show what you expect the output data set to look like.