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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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