Find Earliest Date in Latest Group

Accepted Solution Solved
Reply
Regular Contributor
Posts: 247
Accepted Solution

Find Earliest Date in Latest Group

I am trying to find the first date of the latest regimen in a group of treatments.  I'm having a hard time wrapping my head around this.  I have a feeling PROC SQL with a HAVING statement might be my best bet, but I'm not able to think of how to do this.

 

Suppose I have several subjects who have received various regimens of therapy where each regimen can contain multiple therapies.  I want to find the latest date for each subject.  Then, I need to find the earliest date in that corresponding regimen.

 

So, in the data below, Subject 101-001 has a latest date of 04JUL2014, which is Regimen #2.  The earliest date in Regimen #2 is 10JUN2014.  That would be the record I want to keep.  Likewise, Subject 101-002 has a latest date of 21DEC2016, which is in Regimen #2.  The earliest date in Regimen #2 is 15NOV2016.  That would be the record I would want to keep.

 

I can't make any assumptions like regimens being in chronological order.  It could very well happen that Regimen #2 happens a couple years before Regimen #1.  Regimens are the order they are entered more so than when they occurred.

 

Subject Regimen Date

101-001112-Dec-2013
101-001121-Dec-2013
101-00115-Jan-2014
101-001210-Jun-2014
101-001219-Jun-2014
101-00124-Jul-2014
101-002127-Sep-2016
101-00211-Oct-2016
101-002215-Nov-2016
101-002221-Dec-2016
101-002320-Nov-2016
101-00231-Dec-2016

Accepted Solutions
Solution
‎05-23-2018 09:56 AM
PROC Star
Posts: 1,848

Re: Find Earliest Date in Latest Group

Posted in reply to djbateman
data have;
input Subject $ Regimen Date :date9.;
format date date9.;
cards;
101-001	1	12-Dec-2013
101-001	1	21-Dec-2013
101-001	1	5-Jan-2014
101-001	2	10-Jun-2014
101-001	2	19-Jun-2014
101-001	2	4-Jul-2014
101-002	1	27-Sep-2016
101-002	1	1-Oct-2016
101-002	2	15-Nov-2016
101-002	2	21-Dec-2016
101-002	3	20-Nov-2016
101-002	3	1-Dec-2016
;
proc sql;
create table want as
select a.*
from have a, (select subject,regimen as _regimen  from have group by subject having date=max(date)) b 
where a.subject=b.subject
group by a.subject,regimen,_regimen 
having date=min(date) and _regimen=regimen;
quit;

View solution in original post


All Replies
Solution
‎05-23-2018 09:56 AM
PROC Star
Posts: 1,848

Re: Find Earliest Date in Latest Group

Posted in reply to djbateman
data have;
input Subject $ Regimen Date :date9.;
format date date9.;
cards;
101-001	1	12-Dec-2013
101-001	1	21-Dec-2013
101-001	1	5-Jan-2014
101-001	2	10-Jun-2014
101-001	2	19-Jun-2014
101-001	2	4-Jul-2014
101-002	1	27-Sep-2016
101-002	1	1-Oct-2016
101-002	2	15-Nov-2016
101-002	2	21-Dec-2016
101-002	3	20-Nov-2016
101-002	3	1-Dec-2016
;
proc sql;
create table want as
select a.*
from have a, (select subject,regimen as _regimen  from have group by subject having date=max(date)) b 
where a.subject=b.subject
group by a.subject,regimen,_regimen 
having date=min(date) and _regimen=regimen;
quit;
Esteemed Advisor
Posts: 5,543

Re: Find Earliest Date in Latest Group

Posted in reply to djbateman

If you would rather not use SQL:

 

proc sort data=have; by subject regimen date; run;

proc means data=have noprint;
by subject;
output out=temp(drop=_: ) maxid(date(regimen))=  / autoname;
run;

data want;
merge have temp(in=ok); by subject regimen;
if ok and first.regimen then output;
run;
PG
PROC Star
Posts: 266

Re: Find Earliest Date in Latest Group

Posted in reply to djbateman

If you only want the date (and not other variables from the first observation in the regimen), it can be done in a single datastep like this:

data want;

  do until(last.subject);

    set have;

    by subject regimen;

    if first.regimen then

      first_date=date;

    end;

  date=first_date;

  drop first_date;

run;

Super User
Posts: 10,788

Re: Find Earliest Date in Latest Group

Posted in reply to djbateman
data have;
input Subject $ Regimen Date :date9.;
format date date9.;
cards;
101-001	1	12-Dec-2013
101-001	1	21-Dec-2013
101-001	1	5-Jan-2014
101-001	2	10-Jun-2014
101-001	2	19-Jun-2014
101-001	2	4-Jul-2014
101-002	1	27-Sep-2016
101-002	1	1-Oct-2016
101-002	2	15-Nov-2016
101-002	2	21-Dec-2016
101-002	3	20-Nov-2016
101-002	3	1-Dec-2016
;
data temp;
 set have;
 by Subject  Regimen notsorted;
 group+first.Regimen;
run;
proc sort data=temp;
by Subject group descending date;
run;
data want;
 set temp;
 by Subject;
 if last.Subject;
run;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 145 views
  • 0 likes
  • 5 in conversation