BookmarkSubscribeRSS Feed
TheMajewski
Calcite | Level 5

First time poster - frequent lurker, many thanks to all who have answered questions in the past I was able to use and reference!

 

I have variables with membership that has a Start & End Dates and looking to get the MIN and MAX without breaks

 

Example:

Member    Start                End

1               01/01/2015     03/12/2015

1               03/13/2015     07/02/2015

1               07/03/2015     10/01/2015

2               01/01/2015      04/01/2015

2              04/02/2015       05/01/2015

2              12/01/2015       12/31/2015

 

I want the output to be below:

Member   Start               End

1              01/01/2015     10/01/2015

2              01/01/2015       05/01/2015

2              12/01/2015       12/31/2015

 

Any help is greatly appreciated!

 

4 REPLIES 4
qoit
Pyrite | Level 9

Hi, does the below help?

 

data have;
	infile datalines;
	input member $ start:mmddyy10. end:mmddyy10.;
	format start end mmddyy10.;
	datalines;
1 01/01/2015 03/12/2015
1 03/13/2015 07/02/2015
1 07/03/2015 10/01/2015
2 01/01/2015 04/01/2015
2 04/02/2015 05/01/2015
2 12/01/2015 12/31/2015
;
run;

proc means data=have nway noprint;
	class member;
	var start end;
	output out=want (drop=_freq_ _type_ end_min start_max rename=(start_min = start end_max=end))  min= max= /autoname;
run;
Kurt_Bremser
Super User

SQL is not suited for this, the DATA step is much better at dealing with sequences:

data have;
input Member $ Start :mmddyy10. End :mmddyy10.;
format start end yymmdd10.;
datalines;
1 01/01/2015 03/12/2015
1 03/13/2015 07/02/2015
1 07/03/2015 10/01/2015
1 10/03/2015 10/31/2015
2 01/01/2015 04/01/2015
2 04/02/2015 05/01/2015
2 12/01/2015 12/31/2015
;

data want;
merge
  have
  have (
    firstobs=2
    keep=member start
    rename=(member=_member start=_start)
  )
;
retain fstart;
if member ne lag(member) then fstart = start;
if member ne _member or _start gt end + 1
then do;
  start = fstart;
  output;
  fstart = _start;
end;
drop fstart _:;
run;

I added an additional observation for member 1 to show how the code deals with a break in membership, and when a single observation constitutes one period.

Ksharp
Super User
data have;
input Member $ Start :mmddyy10. End :mmddyy10.;
format start end yymmdd10.;
datalines;
1               01/01/2015     03/12/2015
1               03/13/2015     07/02/2015
1               07/03/2015     10/01/2015
2               01/01/2015      04/01/2015
2              04/02/2015       05/01/2015
2              12/01/2015       12/31/2015
;
data temp;
 set have;
 do date=start to end;
  output;
 end;
 keep member date;
run;
proc sort data=temp nodupkey;
by member date;
run;
data temp;
 set temp;
 by member;
 if first.member or dif(date) ne 1 then group+1;
run;
proc sql;
create table want as
select member,group,min(date) as start format=mmddyy10.,max(date) as end format=mmddyy10.
 from temp
  group by member,group;
quit;
ChrisNZ
Tourmaline | Level 20

Another way:

data HAVE;
input MEMBER $  START :mmddyy10. END :mmddyy10.;
format START END yymmdd10.;
datalines;
1 01/01/2014 03/12/2014
1 01/01/2015 03/12/2015
1 03/13/2015 07/02/2015
1 07/03/2015 10/01/2015
1 10/03/2015 10/31/2015
2 01/01/2015 04/01/2015
2 04/02/2015 05/01/2015
2 12/01/2015 12/31/2015
;
data WANT;
  set HAVE;
  by MEMBER;
  _END=lag(END);                            
  if first.MEMBER then _START=START ;  
  else if START-_END ne 1 then do;    
    S=_START; E=_END; output;                           
    _START=START;
  end;
  if last.MEMBER then do;       
    S=_START; E=END; output;                           
  end; 
  retain _START;
  keep S E MEMBER;
  format S E date9. ;
run;

MEMBER   S   E
1 01JAN2014 12MAR2014
1 01JAN2015 01OCT2015
1 03OCT2015 31OCT2015
2 01JAN2015 01MAY2015
2 01DEC2015 31DEC2015

 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 408 views
  • 0 likes
  • 5 in conversation