topic Re: Roll up monthly enrollment data and create continuous enrollment segment in SAS Programming

Roll up monthly enrollment data and create continuous enrollment segment

buszhangsy — Thu, 30 Aug 2018 13:55:15 GMT

Hello,

I have monthly enrollment data and would like to roll them up into continuous enrollment segments. Note that some overlap in monthly enrollment data can happen because they are from different sources. I tried to create a date variable, that is, do date=start to end (30 lines if monthly enrollment is Sep01 to Sep30) and check if dif(date)>1 for triggering discontinuation of current enrollment. However, my data set gets extremely large (300B obs) and run time is several hours. I would appreciate if anyone could provide a much efficient approach to tackle this. An example of data input and desired output are shown below. Thanks!

Data input

PatientID Start End

00001 01/01/2018 01/31/2018

00001 02/01/2018 02/28/2018

00001 04/01/2018 04/30/2018

00001 05/01/2018 05/20/2018

00001 05/15/2018 05/31/2018

Output

PatientID Start End

00001 01/01/2018 02/28/2018

00001 04/01/2018 05/31/2018

Re: Roll up monthly enrollment data and create continuous enrollment segment

AMSAS — Thu, 30 Aug 2018 17:03:49 GMT

Hi,

This can be achieved by using the retain statement and only outputting when there's a gap in the dates

Here's an example using your sample data:

data source ;
	infile cards ;
	input pid $ start :mmddyy. end :mmddyy. ;
	format start date9. end date9. ;
cards ;
00001      01/01/2018      01/31/2018       
00001      02/01/2018      02/28/2018         
00001      04/01/2018      04/30/2018      
00001      05/01/2018      05/20/2018    
00001      05/15/2018      05/31/2018   
;
run ;

data output ;
	retain start_date end_date ;
	format start_date date9. end_date date9. ;
	set source end=eof ;
	if _n_=1 then do ;
		start_date=start ;
		end_date=end ;
	end ;
	else do ;
		if start>end_date+1 then do ;
			output ;
			start_date=start ;
			end_date=end ;
		end ;
		else do ;
			end_date=end ;
		end ;
	end ;
	if eof then
		output ;
run ;

Re: Roll up monthly enrollment data and create continuous enrollment segment

buszhangsy — Thu, 30 Aug 2018 18:22:54 GMT

Thanks AMSAS! I do have a question. There will be some time periods with overlaps (see below for another example), how should I sort the dataset in order to use the code you provided? As you can see, when SAS processes the last record, it will keep the start_date previously stored but reassign end_date to be 05/20/2018, which is supposed to be 05/31/2018.

00001      01/01/2018      01/31/2018       
00001      02/01/2018      02/28/2018         
00001      04/01/2018      04/30/2018      
00001      05/01/2018      05/31/2018    
00001      05/15/2018      05/20/2018

Re: Roll up monthly enrollment data and create continuous enrollment segment

AMSAS — Thu, 30 Aug 2018 22:01:37 GMT

Hi,
Yes, the code needed some minor changes, to reflect the difference in the input data. Also it needed a change to cope with multiple patient_id
Here's updated code, I think this should do what you are looking for

data source ;
	infile cards ;
	input pid $ start :mmddyy. end :mmddyy. ;
	format start date9. end date9. ;
cards ;
00001      01/01/2018      01/31/2018       
00001      02/01/2018      02/28/2018         
00001      04/01/2018      04/30/2018      
00001      05/01/2018      05/21/2018    
00001      05/15/2018      05/31/2018   
00002      01/01/2018      01/31/2018       
00002      02/01/2018      02/28/2018         
00002      04/01/2018      04/30/2018      
00002      05/01/2018      05/31/2018    
00002      05/15/2018      05/20/2018   
;
run ;

proc sort ;
	by pid start end ;
run ;

data output ;
	retain start_date end_date ;
	format start_date date9. end_date date9. ;
	set source ;
	by pid start end ;
	if first.pid=1 then do ;
		start_date=start ;
		end_date=end ;
	end ;
	else do ;
		if start>end_date+1 then do ;
			output ;
			start_date=start ;
			end_date=end ;
		end ;
		else if end>end_date then do ;
			end_date=end ;
		end ;
	end ;
	if last.pid then
		output ;
run ;