Get right duration from multiple startdate & stopdate per person? proc sql / sas eg

[j_l1]

Hi all,

I have a cohort with their medication start date, end date and ID. I'm trying to identify new users in 2017, ie. no other prescription prior 1 year from their index date or they have a gap longer than a year is also qualified.

1st I pulled anyone with earliest startdate in 2017 (recode as indexdate),  then if any ID has an indexdate that's between  indexdate-365 and indexdate-1, then they are flagged as ineligible.

proc sql;
create table NewUserin2017_flag
as
    select UserwithMedsin2017.patientuid,
    sum (case when
        (startdate between indexdate-365 and indexdate-1)
        then 1
        else 0
        end) as ineligible
    from UserwithMedsin2017
    left join work.patmedcatwithoutdupes Meds
    on UserwithMedsin2017.patientuid=Meds.patientuid
    group by UserwithMedsin2017.patientuid
    having ineligible=0
;

That's how I got my cohort. Then I was just looking through the raw data at one point, notice something I missed before, the raw data looks like below. For ID=1, 1st ob startdate=7/12/2016, 2nd ob startdate=10/14/2017, the gap is definitely over 1 year, which made this person qualified based on my calculation. However, when you look at the stopdate=10/14/2017. So he/she basically stopped this drug on that day, then got a new prescription on the same day. Which means this person never stopped the drug, ie. no gap between these two prescriptions, when you also look at stopdate. Sometime they definitely qualifies the way I define them, eg. ID2 & ID3.

It is rare to have a prescription over 1 year, but that one record just got stuck in my head, and I couldn't figure out a way to account this factor in my code. Could you help me? either SAS EG or proc sql is okay. How to count the stopdate into my code, to actually get the correct cohort, and sometime the enddate is missing. Is this a rolling date question? I don't even sure what type of question this is.

data sample;
  informat startdate mmddyy10.;
  informat enddate mmddyy10.;
  input ID startdate enddate;
  format startdate enddate mmddyy10.;
  datalines;
   
1 7/12/2016  10/14/2017    
1 10/14/2017   .         
2 6/02/2014  12/17/2014 
2 12/22/2017 7/8/2018   
3 6/02/2016   .          
3 7/22/2017  7/8/2018    

I hope my long message makes sense to you. Thanks very much! I appreciate any hints and advice.

J.

[ballardw]

Can you show what the result for that example data should look like?