Defining insurance coverage status based on the varying time range around the date of diagnosis

[Cruise]

Dear SAS experts,

I'd like to identify whether patients had insurance at the time of diagnosis or varying time range around the time of diagnosis. I have a solution so far to identify whether patients had insurance at the time of diagnosis (see code section). Now, I'd like to make my algorithm more dynamic or capable to check the status of insurance coverage as following:

coverage status is 1 if patient had insurance at the time of diagnosis (solved and see the code below)

https://communities.sas.com/t5/SAS-Programming/Select-date-pair-that-includes-the-fixed-date/m-p/551...

coverage status is 1 if patient had insurance 15 days before or after diagnosis

coverage status is 1 if patient had insurance 30 days before or after diagnosis

coverage status is 1 if patient had insurance 60 days before or after diagnosis

.. thru

coverage status is 1 if patient had insurance 180 days before or after diagnosis

About my data:

- My data has date pairs (elig_beg and elig_end) for varying length of time intervals to mark insurance coverage periods. Everybody in this dataset has insurance, but, on and off basis. For example: 01/05/2005(elig_beg1)-01/08/2005(elig_end1) and 01/01/2007(elig_beg2)-01/01/2010(elig_end2). This patient had two coverage intervals. First coverage lasted 3 months and the last coverage lasted 3 years.

- Date_diagnosis : date of diagnosis of a disease

- ID: unique patient identifier

- Eligibility data in separate columns (elig_beg1 thru 50 and elig_end1 thru 50) and unique for each patients.

Image of the mock data "have" shows the nearest date-pair to the 'date_diagnosis' and the desired output which is around 12 columns with the status (1 or missing or zero) for each patient. For example, column "col 0" is entirely missing because none of patients fulfilled the criteria to have insurance at the time of diagnosis. However, COV180 has missing only for the patients ID 1 and 5 given the eligibility criteria: whether patients had insurance 6 month before or after date_diagnosis. Please note that I only filled the columns cov0 and cov180 for demo purpose.

DATA HAVE;
format id 8. DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9.; 
informat DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9.; 
input ID DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8; 
cards;
1 29MAR2005 01NOV2003 30NOV2003 01JAN2004 31JUL2004 01OCT2004 31DEC2004 01JUN2005 31JUL2005 01OCT2005 31JAN2006 01FEB2011 30JUN2011 01JAN2014 28FEB2014 01JAN2015 31JAN2017 
2 08JUN2005 01JAN2002 31JAN2002 01DEC2005 28FEB2013 01DEC2016 31DEC2016 01OCT2017 30NOV2017 . . . . . . . . 
3 24JUN2005 01JAN2002 31JAN2002 01AUG2002 30SEP2002 01NOV2004 31DEC2004 01JUL2011 31AUG2011 . . . . . . . . 
4 18JUL2005  01AUG2005 31DEC2006 01DEC2009 31DEC2010 . . . . . . . . . . . . 
5 15AUG2005 01MAY2002 31MAY2002 01MAR2003 31MAY2003 01OCT2003 30NOV2003 01JAN2004 29FEB2004 01APR2004 30APR2004 01APR2006 31MAY2006 01OCT2006 30NOV2006 . . 
6 15SEP2005 01MAR2005 31MAR2005 01FEB2006 28FEB2006 . . . . . . . . . . . . 
7 17OCT2005 01MAR2005 31MAY2005 01JAN2006 28FEB2006 01FEB2007 28FEB2007 . . . . . . . . . . 
8 30OCT2005 01JUN2005 30JUN2005 01AUG2005 30SEP2005 01MAY2006 30JUN2006 01NOV2009 31DEC2009 01MAY2011 30NOV2011 01JAN2013 28FEB2013 01DEC2013 30NOV2014 01MAR2015 31MAY2015 
9 10NOV2005 01JUN2004 30JUN2004 01SEP2004 31OCT2004 01MAY2005 30JUN2005 01SEP2006 30NOV2006 01FEB2007 30NOV2007 01NOV2008 31DEC2008 01APR2009 30APR2009 . . 
10 17NOV2005 01MAY2005 31JUL2005 01APR2006 31MAY2006 01APR2009 31MAY2009 01APR2010 30APR2010 01APR2011 30JUN2011 01MAR2012 30APR2012 01MAR2013 31MAR2013 01DEC2016 31DEC2016 
;
proc print; run; 

data want (drop=i); set have; 
  array beg {*} elig_beg:;
  array end {*} elig_end:;
  format cov_beg cov_end date9.;
  do i = 1 to dim(beg);
    if missing(beg{i}) then leave;
	if beg{i} <= DATE_DIAGNOSIS <= end{i} then do;
	    cov_beg = beg{i};
    	cov_end = end{i};
 		leave;
	end;
  end;
run;

PROC PRINT; RUN; 

[ChrisNZ]

Just calculate the distance (counted in number of days) and then you can do what you want.

data WANT;  
  set HAVE;
  array BEG {*} ELIG_BEG:;
  array END {*} ELIG_END:;
  do I = 1 to dim(BEG) while (^missing(BEG{i}));
    %* insured;
    if BEG{I} <= DATE_DIAGNOSIS <= END{I} then do;
      DIST=0; 
      leave;
    end;
    %* before first insured period;
    if I = 1 then if DATE_DIAGNOSIS < BEG{I} then do;
      DIST=  BEG{I} - DATE_DIAGNOSIS ; 
      leave;
    end;
    %* between 2 insured periods;
    if I > 1 then if END{I-1} < DATE_DIAGNOSIS < BEG{I} then do;
      DIST=min( DATE_DIAGNOSIS - END{I-1},  BEG{I} - DATE_DIAGNOSIS ); 
      leave;
    end;
    %* after last insured period: you add it! :) ;
  end;
run; 

ID	DATE_DIAGNOSIS	DIST
1	29MAR2005	64
2	08JUN2005	176
3	24JUN2005	175
4	18JUL2005	14
5	15AUG2005	229
6	15SEP2005	139
7	17OCT2005	76
8	30OCT2005	30
9	10NOV2005	133
10	17NOV2005	109

[ErikLund_Jensen · Accepted solution]

Hi @Cruise 

Next try - Thise code handles diagnosis dates in coverage intervals as well as before/after/between intervals, so it replaces the code from yesterday. Example data is expanded to cover both.

There is a problem in your example data, where ID = 10 has elig_end8 = '31DEC', which is read as a missing value. Is it a typo in your example data, that doesn't occur in real life, or should invalid dates be handled somehow? And I guess that some patients have a current valid insurance coverage, so there is no "natural" elig_end. how is that expressed in data?

I ask these questions because the code I posted yesterday doesn't work if the last interval has a missing elig_end, and the diagnosis date is in that interval, and the same problem arises with the problem in your recent post, where the number of days between last elig_end and a diagnosis date after that can't be determined in case of missing elig_end.

I have coded a workaround, so a missing elig_end in the last interval as interpreted as "forever, so in that case a patient is coded as covered, i.e. cov0 = 1, if the diagnosis date is after elig_beg in the last interval.

In your previous post you wanted the actual insurance interval that covers a given patient id included in output, and I suppose you would also want the nearest insurance interval together with the number of days before/after, so I included that in the code. 

DATA HAVE;
format id 8. DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9.; 
informat DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8 date9.; 
input ID DATE_DIAGNOSIS elig_beg1 elig_end1 elig_beg2 elig_end2 elig_beg3 elig_end3 elig_beg4 elig_end4 elig_beg5 elig_end5 elig_beg6 elig_end6 elig_beg7 elig_end7 elig_beg8 elig_end8; 
cards;
1 29MAR2005 01NOV2003 30NOV2003 01JAN2004 31JUL2004 01OCT2004 31DEC2004 01JUN2005 31JUL2005 01OCT2005 31JAN2006 01FEB2011 30JUN2011 01JAN2014 28FEB2014 01JAN2015 31JAN2017 
2 08JUN2005 01JAN2002 31JAN2002 01DEC2005 28FEB2013 01DEC2016 31DEC2016 01OCT2017 30NOV2017 . . . . . . . . 
3 24JUN2005 01JAN2002 31JAN2002 01AUG2002 30SEP2002 01NOV2004 31DEC2004 01JUL2011 31AUG2011 . . . . . . . . 
4 18JUL2005  01AUG2005 31DEC2006 01DEC2009 31DEC2010 . . . . . . . . . . . . 
5 15AUG2005 01MAY2002 31MAY2002 01MAR2003 31MAY2003 01OCT2003 30NOV2003 01JAN2004 29FEB2004 01APR2004 30APR2004 01APR2006 31MAY2006 01OCT2006 30NOV2006 . . 
6 15SEP2005 01MAR2005 31MAR2005 01FEB2006 28FEB2006 . . . . . . . . . . . . 
7 17OCT2005 01MAR2005 31MAY2005 01JAN2006 28FEB2006 01FEB2007 28FEB2007 . . . . . . . . . . 
8 30OCT2005 01JUN2005 30JUN2005 01AUG2005 30SEP2005 01MAY2006 30JUN2006 01NOV2009 31DEC2009 01MAY2011 30NOV2011 01JAN2013 28FEB2013 01DEC2013 30NOV2014 01MAR2015 31MAY2015 
9 10NOV2005 01JUN2004 30JUN2004 01SEP2004 31OCT2004 01MAY2005 30JUN2005 01SEP2006 30NOV2006 01FEB2007 30NOV2007 01NOV2008 31DEC2008 01APR2009 30APR2009 . . 
10 17NOV2018 01MAY2005 31JUL2005 01APR2006 31MAY2006 01APR2009 31MAY2009 01APR2010 30APR2010 01APR2011 30JUN2011 01MAR2012 30APR2012 01MAR2013 31MAR2013 01DEC2016 31DEC
11 10DEC2004 01NOV2004 30JUN2008 01OCT2008 30JUN2011 . . . . . . . . . . . . 
12 14DEC2004 01NOV2004 31OCT2008 . . . . . . . . . . . . . . 
13 01DEC2008 01NOV2004 31OCT2008 01DEC2008 31DEC2016 . . . . . . . . . . . . 
14 24DEC2004 01NOV2004 30JUN2005 . . . . . . . . . . . . . . . .
15 01DEC2004 01NOV2005 31JAN2017 . . . . . . . . . . . . . . . .
16 22DEC2005 01NOV2005 31DEC2016 . . . . . . . . . . . . . . . .
17 03DEC2009 01NOV2009 31DEC2016 01NOV2017 31DEC2017 . . . . . . . . . . . . 
18 01DEC2010 01NOV2010 31JUL2011 01NOV2013 31OCT2014 . . . . . . . . . . . . 
;

data want (drop=imax i days_before days_after dist); set have; 
	format cov_beg cov_end date9. cov_not 8.;
	array cov cov0 cov15 cov30 cov60 cov90 cov120 cov150 cov180;
	array beg {*} elig_beg: ;
	array end {*} elig_end:;

	imax = dim(beg) - nmiss(of elig_beg:);

	* Diagnosis in intervals;
	do i = 1 to imax;
		if beg{i} <= DATE_DIAGNOSIS <= min(end{i},'31dec9999'd) then do;
			cov_beg = beg{i};
			cov_end = end{i};
			dist = 0;
			leave;
		end;
	end;

	* No interval found;
	if cov_beg = . then do;

		* Diagnosis before first start;
		if DATE_DIAGNOSIS < beg{1} then do;
			cov_beg = beg{1};
			cov_end = end{1};
			dist = beg{1} - DATE_DIAGNOSIS;
		end;

		* Diagnosis after last end - only if last start is ended;
		else if DATE_DIAGNOSIS > end{imax} and end{imax} ne . then do;
			cov_beg = beg{imax};
			cov_end = end{imax};
			dist = DATE_DIAGNOSIS - end{imax};
		end;

		* Diagnosis in gap between intervals;
		else do i = 2 to imax;
			if end{i-1} < DATE_DIAGNOSIS < beg{i} then do;
				days_before = DATE_DIAGNOSIS - end{i-1};
				days_after = beg{i} - DATE_DIAGNOSIS;
				if days_after > days_before then do;
					cov_beg = beg{i-1};
					cov_end = end{i-1};
					dist = days_before;
				end;
				else do;
					cov_beg = beg{i};
					cov_end = end{i};
					dist = days_after;
				end;
				leave;
			end;
		end;
	end;

	* Set code for date distance;
	if dist = 0 then cov{1} = 1;
	else if dist <= 15 then cov{2} = 1;
	else if dist <= 180 then cov{int((dist-1)/30)+3} = 1;
	else cov_not = 1;
run;

[Cruise]

@ErikLund_Jensen 

1. Sorry, that’s a typo, in real-data it is: 31DEC2016 and I just edited the mock data
2. There is no missing in elig_end in the real-data as long as there’re non-missing end_beg:
3. Yes, my previous post was about to mark patients whose date_diagnosis fell in the any of date pairs. However, after I subset those patients who had insurance at the time of diagnosis, the remaining data included patients who didn’t have insurance at the time of diagnosis with varying distance between their elig_beg: and elig_end to the date_diagnosis. Then question became, whether the rate of case identification from this insurance claim data vary as a function of time spans around the date_diagnosis. At the end of this exercise, I’m hoping to come up with a simple linear regression plot.
Hope this makes sense?

[Cruise]

Dear Erik, do you mind to update the algorithm assuming there is no missing in elg_end: dates? please? Thanks a lot. I just don't want to make a careless touch to your precious algorithm here.

[ErikLund_Jensen]

Dear Cruise

I don't mind at all, I'm glad to help. I changed min(end{i},'31dec9999'd) to end{i}, that is the only modification needed.

I could not resist the temptation to rewrite the code to make it simpler (only one loop over intervals) and more straightforward in the logic, and I changed the algoritm for setting the right cov_nn variable to make it easier to maintain. You can see what happens if you just change 

array cov cov0 cov15 cov30 cov60 cov90 cov120 cov150 cov180 cov_plus;

to

array cov cov0 cov15 cov30 cov60 cov_plus;

or 

array cov cov0 cov15 cov30 cov60 cov90 cov120 cov150 cov180 cov210 cov240 cov_plus;

data want3 (drop = Interval_count i days_before days_after dist); set have; 
	format cov_beg cov_end date9.;
	array cov cov0 cov15 cov30 cov60 cov90 cov120 cov150 cov180 cov_plus;
	array beg {*} elig_beg: ;
	array end {*} elig_end:;

	* loop over non-missing intervals;
	Interval_count = dim(beg) - nmiss(of elig_beg:);
	do i = 1 to Interval_count;
		cov_beg = beg{i};
		cov_end = end{i};

		* Diagnosis in insurance interval;
		if beg{i} <= DATE_DIAGNOSIS <= end{i} then dist = 0;

		* Diagnosis before first interval start;
		else if i = 1 and DATE_DIAGNOSIS < beg{i} then dist = beg{1} - DATE_DIAGNOSIS;

		* Diagnosis after last interval end;
		else if i = Interval_count and DATE_DIAGNOSIS > end{Interval_count} then dist = DATE_DIAGNOSIS - end{i};

		* Diagnosis in gap between intervals - find closest interval;
		else if i > 1 then do;
			if end{i-1} < DATE_DIAGNOSIS < beg{i} then do;
				days_before = DATE_DIAGNOSIS - end{i-1};
				days_after = beg{i} - DATE_DIAGNOSIS;
				if days_after > days_before then do;
					cov_beg = beg{i-1};
					cov_end = end{i-1};
					dist = days_before;
				end;
				else dist = days_after;
			end;
		end;

		* drop out of loop after first match to save ressources;
		if dist ne . then leave;
	end;

	* Set code for date distance - all "overshoot" goes into last defined cov_array variable;
	if dist = 0 then cov{1} = 1;
	else if dist <= 15 then cov{2} = 1;
	else cov{min(dim(cov), int((dist-1)/30)+ 3)} = 1;
run;

[Cruise]

Thank you so much Erik!

[Cruise]

@ErikLund_Jensen @ChrisNZ 

I've been working on the analysis for a while using the data produced from the SAS algorithm you kindly provided. Would you please help me modify the code to find a time to the next coverage? In other words, the distance between date_diagnosis and the next elig_beg?

DISTANCE=15SEP2005 - 01FEB2006. 

Current algorithm would calculate 15SEP2005-31MAR2005 because distance here is smaller than 15SEP2005 - 01FEB2006. But I don't need 15SEP2005 - 01FEB2006. 

I greatly appreciate your time.

I also posted this question in new form here:

https://communities.sas.com/t5/SAS-Programming/How-long-it-took-patients-to-get-back-an-insurance/m-...

[ErikLund_Jensen]

Hi @Cruise 

No problem. Here is the modified code with 2 new variables in output: days_before and days_after. Note following rules:

- both set missing value if the diagnosis date is within an interval.

- days_after set missing if the diagnosis date is before the first interval.

- days_before set missing if the diagnosis date is after the last interval.

data want3 (drop = Interval_count i); set have; 
	format cov_beg cov_end date9.;
	array cov cov0 cov15 cov30 cov60 cov90 cov120 cov150 cov180 cov_plus;
	array beg {*} elig_beg: ;
	array end {*} elig_end:;

	* loop over non-missing intervals;
	Interval_count = dim(beg) - nmiss(of elig_beg:);
	do i = 1 to Interval_count;
		cov_beg = beg{i};
		cov_end = end{i};

		* Diagnosis in insurance interval;
		if beg{i} <= DATE_DIAGNOSIS <= end{i} then dist = 0;

		* Diagnosis before first interval start;
		else if i = 1 and DATE_DIAGNOSIS < beg{i} then do; 
			dist = beg{1} - DATE_DIAGNOSIS;
			days_before = beg{i} - DATE_DIAGNOSIS;
		end;

		* Diagnosis after last interval end;
		else if i = Interval_count and DATE_DIAGNOSIS > end{Interval_count} then do;
			dist = DATE_DIAGNOSIS - end{i};
			days_after = DATE_DIAGNOSIS - beg{i};
		end;

		* Diagnosis in gap between intervals - find closest interval;
		else if i > 1 then do;
			if end{i-1} < DATE_DIAGNOSIS < beg{i} then do;
				days_after = beg{i} - DATE_DIAGNOSIS;
				days_before = DATE_DIAGNOSIS - end{i-1};
				if days_after > days_before then do;
					cov_beg = beg{i-1};
					cov_end = end{i-1};
					dist = days_before;
				end;
				else dist = days_after;
			end;
		end;

		* drop out of loop after first match to save ressources;
		if dist ne . then leave;
	end;

	* Set code for date distance - all "overshoot" goes into last defined cov_array variable;
	if dist = 0 then cov{1} = 1;
	else if dist <= 15 then cov{2} = 1;
	else cov{min(dim(cov), int((dist-1)/30)+ 3)} = 1;
run;

[Cruise]

Thanks a lot! I appreciate it.