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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.