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.
... View more