Combine Date ranges

[SASTad]

Hello,

My Input Data set

MEM_ID                ACT_BGN         ACT_END

H                         01JUN2015       30JUN2015
H                          01JUL2015       29FEB2016
H                          01MAR2016      31MAR2016
H                          01APR2016       30APR2016
H                          01MAY2016      31MAY2016
H                          01MAR2016      31MAR2016
H                          01APR2016       30APR2016
H                          01MAY2016       31MAY2016

I would like to combine the contiunous date segments into one.

Desired out put:

MEM_ID                ACT_BGN         ACT_END

H                         01JUN2015       31MAY2016

H                         01MAR2016       31MAY2016

Thank you.

[art297 · Accepted solution]

Thought I just answered this in another thread:

The following will work with your test data:

data want (drop=_:);
  set have (rename=(act_bgn=_act_bgn));
  format ACT_BGN date9.;
  retain ACT_BGN;
  by MEM_ID;
  set have ( firstobs = 2 keep = act_bgn rename = (act_bgn = _Next_act_bgn) )
      have (      obs = 1 drop = _all_                                     );
  if first.MEM_ID then act_bgn=_act_bgn;
  if act_end+1 ne _Next_act_bgn then do;
    output;
    act_bgn=_Next_act_bgn;
  end;
run;

HTH,

Art, CEO, AnalystFinder.com

[PGStats]

I would do:

data have;
input MEM_ID $ (ACT_BGN ACT_END) (:date9.);
datalines;
H 01JUN2015 30JUN2015
H 01JUL2015 29FEB2016
H 01MAR2016 31MAR2016
H 01APR2016 30APR2016
H 01MAY2016 31MAY2016
H 01MAR2016 31MAR2016
H 01APR2016 30APR2016
H 01MAY2016 31MAY2016
;

data want;
do until (last.mem_id);
    set have(rename=(act_bgn=bgn act_end=end)); by mem_id;
    act_bgn = coalesce(act_bgn, bgn);
    if not (missing(act_end) or intnx("day", bgn, -1) = act_end) then do;
        output;
        act_bgn = bgn;
        end;
    act_end = end;
    end;
if bgn ne act_bgn then output;
drop bgn end;
format act: yymmdd10.;
run;
    
proc print; run;

[Ksharp]

data have;
input MEM_ID $ (ACT_BGN ACT_END) (:date9.);
format act: date9.;
datalines;
H 01JUN2015 30JUN2015
H 01JUL2015 29FEB2016
H 01MAR2016 31MAR2016
H 01APR2016 30APR2016
H 01MAY2016 31MAY2016
H 01MAR2016 31MAR2016
H 01APR2016 30APR2016
H 01MAY2016 31MAY2016
;

data temp;
 set have;
 by mem_id;
 if first.mem_id or act_bgn-lag(act_end) ne 1 then group+1;
run;
data want;
 set temp;
 by group;
 retain begin;
 if first.group then begin=act_bgn;
 if last.group;
 drop act_bgn group;
 format begin date9.;
run;

[SASTad]

Thank you. This works great.