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.
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
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
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;
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;
Thank you. This works great.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.