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!
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;
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.
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;
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 |
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.