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.
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;
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.