DATA Step, Macro, Functions and more

Combine Date ranges

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Combine Date ranges

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.


Accepted Solutions
Solution
‎03-02-2017 03:22 PM
PROC Star
Posts: 7,366

Re: Combine Date ranges

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

View solution in original post


All Replies
Solution
‎03-02-2017 03:22 PM
PROC Star
Posts: 7,366

Re: Combine Date ranges

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

Respected Advisor
Posts: 4,663

Re: Combine Date ranges

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;



PG
Super User
Posts: 9,691

Re: Combine Date ranges

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;
Occasional Contributor
Posts: 16

Re: Combine Date ranges

Thank you. This works great.

☑ This topic is solved.

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

Discussion stats
  • 4 replies
  • 152 views
  • 2 likes
  • 4 in conversation