topic Re: Manipulating overlapping date claims and shifting them forward in SAS Programming

Manipulating overlapping date claims and shifting them forward

tntmph0 — Fri, 10 Aug 2018 21:13:32 GMT

Hi SAS Community,

I could really use your help! I have overlapping dates that I want to stretch out continuously. Suppose I have the following claims:

rx_start	rx_end
5/28/2014	6/27/2014
6/28/2014	7/28/2014
7/26/2014	8/25/2014
8/24/2014	9/23/2014
9/21/2014	10/21/2014
10/25/2014	11/24/2014
11/22/2014	12/22/2014
12/20/2014	1/19/2015

I want to create a new column of rx start/end dates that doesn't overlap. The first obs doesn't overlap with a prior. Thus, the new column of rx start/end would have the same values. The second obs doesn't overlap with the first; thus, the new column of rx start/end would have the same value as well. The third row overlaps with the prior by two days. So, the new column of rx start/end would shift forward to 7/28/2014 - 8/27/2014. The fourth row overlaps with the NEW rx start/end dates of the prior row (row 3) by 3 days. So, it's new rx start/end date is 8/27/2014 - 9/26/2014. The 5th row overlaps with the 4th row's new dates of 8/27/2014 - 9/26/2014 by 5 days. So, it should shift forward to 9/26/2014 to 10/26/2016. And, so forth and so forth. My current versions of code are:

data temp6;

set temp5;

by person_id class_category molecule strength rx_start rx_end;

retain days_overlap rx_start_new rx_end_new;

rx_start_new=.;

rx_end_new=.;

days_overlap=.;

format rx_start_new rx_end_new mmddyy10.;

if first.person_id then do;

days_overlap=0;

rx_start_new=datepart(rx_start);

rx_end_new=datepart(rx_end);

end;

else do;

days_overlap=max(intck('dtday',rx_start, lag(rx_end_new)),0);

if days_overlap=0 then do;

rx_start_new=datepart(rx_start);

rx_end_new=datepart(rx_end);

end;

else if days_overlap >0 then do;

rx_start_new=intnx('day',datepart(rx_start),days_overlap);

rx_end_new=intnx('day',datepart(rx_end),days_overlap);

end;

run;

AND

data temp3;

set temp2;

by person_id class_category molecule strength rx_start rx_end;

retain days_overlap rx_start_new rx_end_new;

rx_start_new=.;

rx_end_new=.;

lagrx_start=lag(rx_start_new);

lagrx_end=lag(rx_end_new);

days_overlap=.;

format rx_start_new rx_end_new mmddyy10.;

if first.person_id then do;

days_overlap=0;

rx_start_new=datepart(rx_start);

rx_end_new=datepart(rx_end);

end;

else if rx_start < lag(rx_end_new) then do;

days_overlap=intck('dtday',rx_start, lagrx_end);

rx_start_new=intnx('day',rx_start,days_overlap);

rx_start_end=intnx('day',rx_end, days_overlap);

end;

else do;

days_overlap=0;

rx_start_new=datepart(rx_start);

rx_end_new=datepart(rx_end);

output;

end;

run;

Neither is working properly. So, I could really use some advice!

Thanks in advance!

Re: Manipulating overlapping date claims and shifting them forward

novinosrin — Fri, 10 Aug 2018 22:16:48 GMT



data have;
input (rx_start	rx_end) (:mmddyy10.) ;
format rx_start	rx_end mmddyy10.;
cards;
5/28/2014	6/27/2014
6/28/2014	7/28/2014
7/26/2014	8/25/2014
8/24/2014	9/23/2014
9/21/2014	10/21/2014
10/25/2014	11/24/2014
11/22/2014	12/22/2014
12/20/2014	1/19/2015
;

data w1;
set have;
retain _s _e;
if _n_=1 then do;
_s=rx_start;
_e=rx_end;
end;
else do;
if rx_start<_e then do;
k=_e-rx_start;
_s=rx_start+k;
/*not sure either intnx('month',_s,1,'s')-1 or intnx('month',_s,1,'s')*/
_e=intnx('month',_s,1,'s')-1;
end;
else if rx_start>=_e then do;
_s=rx_start;
_e=rx_end;
end;
end;
format _: mmddyy10.;
keep _: k;
run;
/*or */

data w2;
set have;
retain _s _e;
if _n_=1 or rx_start>=_e  then do;
_s=rx_start;
_e=rx_end;
end;
else do;
if rx_start<_e then do;
_s=_e;
/*not sure either intnx('month',_s,1,'s')-1 or intnx('month',_s,1,'s')*/
_e=intnx('month',_s,1,'s')-1;
end;
end;
format _: mmddyy10.;
keep _:;
run;

Or both garbage 😞

Re: Manipulating overlapping date claims and shifting them forward

ballardw — Fri, 10 Aug 2018 22:54:50 GMT

Neither is working properly. So, I could really use some advice!

Show the desired output for your given input.

Re: Manipulating overlapping date claims and shifting them forward

hashman — Sat, 11 Aug 2018 19:13:59 GMT

@tntmph0:

Your specs look suspicious. You say that you want your overlapping dates to stretch "continuously" (methinks "contiguously" would be a better word), and yet when you have an overlap you want the next range to begin at the date on which the prior range ends. It means that your new output ranges will still overlap, albeit by 1 day. In other words, for two adjacent ranges [1:5] and [3:7], you want the new ranges to be [1:5] and [5:9], though it seems more logical to have the second range as [6:10].

At any rate, you code seems too complex for the purpose. You just need to accumulate the "advance" value with each new overlap and add it to the beginning and end of each original interval. Also, claim files like these are normally grouped by member ID, and each member group is processed independently. To account for that, below, I've expanded your sample input with ID=1 and ID=2. The macro parm GAP=-1 is set per your specs to the (negative) number of days by which you want the new ranges to overlap. If you really want contiguous ranges (the next begins with the day following the end of the prior), set GAP=0. If you want a gap between the new intervals, set GAP to the corresponding positive number of days.

data have ;                                                      
  input id (rx_start rx_end) (:mmddyy10.) ;                      
  cards ;                                                        
1  5/28/2014   6/27/2014                                         
1  6/28/2014   7/28/2014                                         
1  7/26/2014   8/25/2014                                         
1  8/24/2014   9/23/2014                                         
1  9/21/2014  10/21/2014                                         
1 10/25/2014  11/24/2014                                         
1 11/22/2014  12/22/2014                                         
1 12/20/2014   1/19/2015                                         
2  5/28/2014   6/27/2014                                         
2  6/28/2014   7/28/2014                                         
2  7/26/2014   8/25/2014                                         
2  8/24/2014   9/23/2014                                         
2  9/21/2014  10/21/2014                                         
2 10/25/2014  11/24/2014                                         
2 11/22/2014  12/22/2014                                         
2 12/20/2014   1/19/2015                                         
run ;                                                            

%let gap = -1 ; * per your specs ;
                                                                 
data want (drop = _:) ;                                          
  do until (last.id) ;                                           
    set have (rename=(rx_start=_rxs rx_end=_rxe)) ;              
    by id ;                                                      
    _adv = sum (_adv, sum (_p, - _rxs, 1 + &gap) * (_p > _rxs)) ;
    rx_start = _rxs + _adv ;                                     
    rx_end   = _rxe + _adv ;                                     
    output ;                                                     
    _p = _rxe ;                                                  
  end ;                                                          
  format rx: yymmdd10. ;                                         
run ;

If you want no member ID groups involved and process the whole file as a single blob, change the program to:

data want (drop = _:) ;                                          
  do until (last) ;                                              
    set have (rename=(rx_start=_rxs rx_end=_rxe)) end = last ;   
    _adv = sum (_adv, sum (_p, - _rxs, 1 + &gap) * (_p > _rxs)) ;
    rx_start = _rxs + _adv ;                                     
    rx_end   = _rxe + _adv ;                                     
    output ;                                                     
    _p = _rxe ;                                                  
  end ;                                                          
  format rx: yymmdd10. ;                                         
run ;

HTH

Paul D.

Re: Manipulating overlapping date claims and shifting them forward

tntmph0 — Sun, 12 Aug 2018 18:08:22 GMT

What I have
person_id rx_start rx_end
1801 5/28/2014 6/27/2014
1801 6/28/2014 7/28/2014
1801 7/26/2014 8/25/2014
1801 8/24/2014 9/23/2014
1801 9/21/2014 10/21/2014
1801 10/25/2014 11/24/2014
1801 11/22/2014 12/22/2014
1801 12/20/2014 1/19/2015
2001 10/6/2014 11/5/2014
2001 11/4/2014 12/4/2014
2001 12/2/2014 1/1/2015
2001 12/30/2014 1/29/2015
what I want
person_id rx_start rx_end days_overlap rx_start_new rx_end_new
1801 5/28/2014 6/27/2014 0 5/28/2014 6/27/2014
1801 6/28/2014 7/28/2014 0 6/28/2014 7/28/2014
1801 7/26/2014 8/25/2014 2 7/28/2014 8/26/2014
1801 8/24/2014 9/23/2014 2 8/26/2014 9/25/2014
1801 9/21/2014 10/21/2014 4 9/25/2014 10/25/2014
1801 10/25/2014 11/24/2014 0 10/25/2014 11/24/2014
1801 11/22/2014 12/22/2014 2 11/24/2014 12/24/2014
1801 12/20/2014 1/19/2015 4 12/24/2014 1/23/2014
2001 10/6/2014 11/5/2014 0 10/6/2014 11/5/2014
2001 11/4/2014 12/4/2014 1 11/5/2014 12/5/2014
2001 12/2/2014 1/1/2015 3 12/5/2014 1/4/2015
2001 12/30/2014 1/29/2015 5 1/4/2015 2/3/2015
Any thoughts on how I can achieve this?

Re: Manipulating overlapping date claims and shifting them forward

tntmph0 — Sun, 12 Aug 2018 18:23:22 GMT

Hi,

You are correct, in my (clearly failed) attempt to explain my problem, yes,
I meant 'contiguously' and yes, in need it by patient id. Another point is
the gap or days overlap varies depending on the prior claim's 'new date'.
It isn't constant. This is what I have:

person_id rx_start rx_end
1801 5/28/2014 6/27/2014
1801 6/28/2014 7/28/2014
1801 7/26/2014 8/25/2014
1801 8/24/2014 9/23/2014
1801 9/21/2014 10/21/2014
1801 10/25/2014 11/24/2014
1801 11/22/2014 12/22/2014
1801 12/20/2014 1/19/2015
2001 10/6/2014 11/5/2014
2001 11/4/2014 12/4/2014
2001 12/2/2014 1/1/2015
2001 12/30/2014 1/29/2015
This is what I want:

person_id rx_start rx_end days_overlap rx_start_new rx_end_new
1801 5/28/2014 6/27/2014 0 5/28/2014 6/27/2014
1801 6/28/2014 7/28/2014 0 6/28/2014 7/28/2014
1801 7/26/2014 8/25/2014 2 7/28/2014 8/26/2014
1801 8/24/2014 9/23/2014 2 8/26/2014 9/25/2014
1801 9/21/2014 10/21/2014 4 9/25/2014 10/25/2014
1801 10/25/2014 11/24/2014 0 10/25/2014 11/24/2014
1801 11/22/2014 12/22/2014 2 11/24/2014 12/24/2014
1801 12/20/2014 1/19/2015 4 12/24/2014 1/23/2014
2001 10/6/2014 11/5/2014 0 10/6/2014 11/5/2014
2001 11/4/2014 12/4/2014 1 11/5/2014 12/5/2014
2001 12/2/2014 1/1/2015 3 12/5/2014 1/4/2015
2001 12/30/2014 1/29/2015 5 1/4/2015 2/3/2015