BookmarkSubscribeRSS Feed
Pyrite | Level 9


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.;
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


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 ;


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

Super User

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.



Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg



Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 1 reply
  • 2 in conversation