topic Re: PROC SQL: Multiple Varaibles with Dates in Two Columns - Max and Min without Break in SAS Programming

PROC SQL: Multiple Varaibles with Dates in Two Columns - Max and Min without Break

TheMajewski — Wed, 10 Feb 2021 20:28:31 GMT

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!

Re: PROC SQL: Multiple Varaibles with Dates in Two Columns - Max and Min without Break

qoit — Thu, 11 Feb 2021 03:14:11 GMT

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;

Re: PROC SQL: Multiple Varaibles with Dates in Two Columns - Max and Min without Break

Kurt_Bremser — Thu, 11 Feb 2021 07:22:52 GMT

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.

Re: PROC SQL: Multiple Varaibles with Dates in Two Columns - Max and Min without Break

Ksharp — Thu, 11 Feb 2021 15:05:21 GMT

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;

Re: PROC SQL: Multiple Varaibles with Dates in Two Columns - Max and Min without Break

ChrisNZ — Fri, 12 Feb 2021 04:28:34 GMT

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