BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
SASTad
Fluorite | Level 6

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.

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

4 REPLIES 4
art297
Opal | Level 21

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
Opal | Level 21

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
Ksharp
Super User
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
Fluorite | Level 6

Thank you. This works great.

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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