Combining Enrollment Periods

[mosadoluwa]

Hello, 

I'm trying to combine the days of monthly enrollment for patients into an enrollment period.

Here is what I have:

ID Start End Enrol_Period

001 01/01/2015 01/31/2015 1

001 02/01/2015 02/28/2015 1

001 05/01/2015 05/31/2015 2

001 06/01/2015 06/30/2015 2

001 07/01/2015 07/31/2015 2

001 08/01/2015 08/31/2015 2

Similar format for other patients

Here is what I want:

001 01/01/2015 02/28/2015 1

001 05/01/2015 08/31/2015 2

Thank you.

[PeterClemmensen · Accepted solution]

data have;
input ID $ (Start End)(:mmddyy10.) Enrol_Period;
format Start End mmddyy10.;
datalines;
001 01/01/2015 01/31/2015 1
001 02/01/2015 02/28/2015 1
001 05/01/2015 05/31/2015 2
001 06/01/2015 06/30/2015 2
001 07/01/2015 07/31/2015 2
001 08/01/2015 08/31/2015 2
;

data want(keep=ID Start End Enrol_Period);
   set have;
   by Enrol_Period;
   if first.Enrol_Period then s=Start;
   if last.Enrol_Period then do;
      Start=s;
      output;
   end;
   retain s;
run;

[novinosrin]

data have;
input ID $ (Start End)(:mmddyy10.) Enrol_Period;
format Start End mmddyy10.;
datalines;
001 01/01/2015 01/31/2015 1
001 02/01/2015 02/28/2015 1
001 05/01/2015 05/31/2015 2
001 06/01/2015 06/30/2015 2
001 07/01/2015 07/31/2015 2
001 08/01/2015 08/31/2015 2
;


proc sql;
create table want as
select id, min(start) as start format=mmddyy10.,max(end) as end format=mmddyy10.,Enrol_Period
from have
group by id, Enrol_Period;
quit;