DATA Step, Macro, Functions and more

Combine/Compare continuous date segments

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 16
Accepted Solution

Combine/Compare continuous date segments

Hi, I'm dealting with two sets of date segments in a single data set. Please find the example below:

 

MEM_ID             VOID_BGN    VOID_END      ACT_BGN         ACT_END

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

 

I have two date segments in this data set in the form of void and active. For each MEM_ID I'm looking for active date segments that cover the void date segments.

 

In this case void date segments has two date ranges 01JUN2015 to 31MAY2016 and 01MAR2016  to 31MAY2016  and they both are covered by the continuous active segments for this particular MEM_ID. I'm unable to identify such scenarios in my main data set because the date segments are divided and I've failed to create a logic to identify such MEM_ID's. There are many such cases in my main data set. Could you please help me with the code.

 

My desired output should be:

 

MEM_ID             VOID_BGN    VOID_END      ACT_BGN         ACT_END

H                        01JUN2015      31MAY2016   01JUN2015       31MAY2016

H                       01MAR2016     31MAY2016   01MAR2016      31MAY2016

 

 

Thank you for your help.


Accepted Solutions
Solution
‎03-02-2017 03:21 PM
PROC Star
Posts: 7,474

Re: Combine/Compare continuous date segments

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:21 PM
PROC Star
Posts: 7,474

Re: Combine/Compare continuous date segments

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

 

Occasional Contributor
Posts: 16

Re: Combine/Compare continuous date segments

Thank you so much. This works brilliantly.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 137 views
  • 0 likes
  • 2 in conversation