Assigning continuous enrollment dates to members

[Jessy]

Hi,

I have the following data,

data test;

input mem$2. start  end ;

attrib start format =date9. informat=date9.;

attrib end format =date9. informat=date9.;

datalines;

AA 01JAN2015 14FEB2015

AA 15FEB2015 30APR2015

BB 01jan2015 28feb2015

BB 01apr2015 30apr2015

CC 01JAN2015 14FEB2015

CC 15FEB2015 15MAR2015

CC 16MAR2015 30APR2015

DD 01JAN2015 31JAN2015

DD 01MAR2015 31MAR2015

DD 14APR2015 30APR2015

EE 01JAN2015 31JAN2015

FF 01JAN2015 15JAN2015

;

run;

****************************************

And I am trying to output as below,

AA 01JAN2015 30APR2015

BB 01jan2015 28feb2015

BB 01apr2015 30apr2015

CC 01JAN2015 30APR2015

DD 01JAN2015 31JAN2015

DD 01MAR2015 31MAR2015

DD 14APR2015 30APR2015

EE 01JAN2015 31JAN2015

FF 01JAN2015 15JAN2015

************************************

Here is the explanation, for member AA, he had the continuous enrollment

dates from 01jan2015 to 30apr2015 without any gap between the dates.

Hence I need his first and final last dates of enrollment only.

Similar case for member CC.

For member BB and member DD, the enrollment periods are multiple, but

the dates are not continuous, like say BB has a gap for march. so I need

his dates to be the same.

Please suggest.

Thank you in advance

[Ksharp · Accepted solution]

Code: Program

data test;
input mem$2. start  end ;
attrib start format =date9. informat=date9.;
attrib end format =date9. informat=date9.;
datalines;
AA 01JAN2015 14FEB2015
AA 15FEB2015 30APR2015
BB 01jan2015 28feb2015
BB 01apr2015 30apr2015
CC 01JAN2015 14FEB2015
CC 15FEB2015 15MAR2015
CC 16MAR2015 30APR2015
DD 01JAN2015 31JAN2015
DD 01MAR2015 31MAR2015
DD 14APR2015 30APR2015
EE 01JAN2015 31JAN2015
FF 01JAN2015 15JAN2015
;
run;
data temp;
 set test;
 do date=start to end;
  output;
 end;
format date date9.;
drop start end;
run;
data temp;
 set temp;
 by mem;
 dif=dif(date);
 if first.mem then call missing(dif);
run;
data temp;
 set temp;
 by mem;
 if first.mem or dif ne 1 then group+1;
run;
data want;
 set temp;
 by group;
 retain start;
 if first.group then start=date;
 if last.group then do; end=date; output; end;
 format start end date9.;
 drop group date dif;
run;

[Jim_G]

data one;

input mem$2. start  end ;

attrib start format =date9. informat=date9.;

attrib end format =date9. informat=date9.;

datalines;

AA 01JAN2015 14FEB2015

AA 15FEB2015 30APR2015

BB 01jan2015 28feb2015

BB 01apr2015 30apr2015

CC 01JAN2015 14FEB2015

CC 15FEB2015 15MAR2015

CC 16MAR2015 30APR2015

DD 01JAN2015 31JAN2015

DD 01MAR2015 31MAR2015

DD 14APR2015 30APR2015

EE 01JAN2015 31JAN2015

FF 01JAN2015 15JAN2015

;

proc print;  run; 

data two;   set one;  by mem ; 

    format hstart hend testend  date9.;

    retain hstart hend testend;

    drop  start end testend;

if first.mem then do; hstart=start; goto store; end;

else do;

   if start gt testend then do;  output; hstart=start; end;

end;

store: hend=end; testend=end+1;

if last.mem then output;  

proc print; id  mem hstart  hend;  run;    

Jim