Finding the Minimum Date after a Prior Event Date

[Doyinsola]

I have the following data :

DATA have;
        INPUT id dx_dt event_dt1 event_dt2 event_dt3;
        DATALINES;
           id  dx_dt   event_dt1 event_dt2 event_dt3
           1  1/1/2005 1/2/2002  1/4/2007  1/6/2008
           2  3/4/2005 1/5/2006  7/1/2005  1/1/2004
           3  4/4/2006 6/1/2005  1/9/2003  10/1/2007
           4  5/5/2007 1/7/2009  3/1/2008  1/3/2005
           ;
Run;

I would like to find the earliest of the 3 events that happened after the diagnosis date.

I am aware I can use min_date = min (event_dt1, event_dt2, event_dt3) but how do I add a caveat to find the minimum after the dx_dt. So min_date after diagnosis for id1 is 1/4/2007, for id2 is 7/1/2005 and so on.

[Patrick · Accepted solution]

Please try and post tested syntax for creating sample data. This allows us to spend the time supporting you with your actual problem.

data sample;
  input id $ (dx_dt event_dt1 event_dt2 event_dt3) (:mmddyy10.);
  format dx_dt event_dt1 event_dt2 event_dt3 date9.;
  array events event_dt:;
  format min_event_dt date9.;
  do over events;
    if dx_dt<=events then min_event_dt=min(min_event_dt,events);
  end;
  datalines;
1 1/1/2005 1/2/2002 1/4/2007 1/6/2008
2 3/4/2005 1/5/2006 7/1/2005 1/1/2004
3 4/4/2006 6/1/2005 1/9/2003 10/1/2007
4 5/5/2007 1/7/2009 3/1/2008 1/3/2005
;
run;
proc print;
run;

[Kurt_Bremser]

As @Patrick already stated, test your code before posting it here.

Stealing his corrected code, see this:

data sample;
input id $ (dx_dt event_dt1 event_dt2 event_dt3) (:mmddyy10.);
format dx_dt event_dt1 event_dt2 event_dt3 date9.;
datalines;
1 1/1/2005 1/2/2002 1/4/2007 1/6/2008
2 3/4/2005 1/5/2006 7/1/2005 1/1/2004
3 4/4/2006 6/1/2005 1/9/2003 10/1/2007
4 5/5/2007 1/7/2009 3/1/2008 1/3/2005
;
run;

proc transpose data=sample out=trans (drop=_name_ rename=(col1=event_dt));
by id dx_dt;
var event:;
run;

proc sort data=trans;
by id dx_dt event_dt;
run;

data want;
set trans (where=(event_dt > dx_dt));
by id dx_dt;
if first.dx_dt;
run;

Note that the first two steps are only there to bring your data to a sensible structure and order (this should be the natural state of the dataset), so that the actual analysis step becomes very easy.