Data cleaning: index dates

[wj2]

Hello,

I am working with health care data and I need to identify patients who have received 2 or more prescriptions of a group of medications (A, B, C) on different dates within a 1 year period. I would also like to create a variable indicating the index date- defined as the date of the first prescription within the one year period. I have provided an example of my input data set as well as my desired output. Any help with this would be much appreciated!

Have:

Patient_ID	Medication_name	Start_date
1	A	01Jan2011	*Condition not met: only 1 script*
2	B	01Mar2015	*Condition met*
2	B	01April2015
2	B	01May2015
2	B	01Jun2015
3	C	15Feb2016	*Condition met; Note index date is 2nd script b/c first script isn’t within one  year of second script**
3	C	01Jan2018
3	C	01Feb2018
4	B	15May2012	*Condition not met: 2 or more scripts but all >12 months apart*
4	B	01Oct2013
4	B	01Jan2015
5	A	01May2014	*Condition met; Index date is for first period in 2104, not 2016
5	A	01June2014
5	A	20July2014
5	A	01June2016
5	A	01July2016
6	C	25July2018	*Condition not met: scripts not on different dates*
6	C	25July2018
7	A	01Aug2019	*Condition met*
7	B	01Sep2019

Want:

Patient_ID	Medication_name	Start_date	Index_date
2	B	01Mar2015	01Mar2015	*Condition met*
2	B	01April2015	01Mar2015
2	B	01May2015	01Mar2015
2	B	01Jun2015	01Mar2015
3	C	15Feb2016	01Jan2018	*Condition met; Note index date is 2nd script b/c first script isn’t within one  year of second script*
3	C	01Jan2018	01Jan2018
3	C	01Feb2018	01Jan2018
5	A	01May2014	01May2014	*Condition met; Index date is for first period in 2104, not 2016
5	A	01June2014	01May2014
5	A	20July2014	01May2014
5	A	01June2016	01May2014
5	A	01July2016	01May2014
7	A	01Aug2019	01Aug2019	*Condition met*
7	B	01Sep2019	01Aug2019

[PaigeMiller]

So what happens if patient_ID 6 script A has the following dates

01JAN2018

01AUG2018

07FEB2019

The 07FEB2019 is within a one year period of 01AUG2018, and the 01AUG2018 is within one year of 01JAN2018. How is that handled?

Can a patient_ID have more than one script? How would that be handled?

[wj2]

@PaigeMiller Good questions. Regarding the first scenario, 01JAN2018 would be the index date because it is the first date within the first one year period that the condition is met. 

Yes, patients can have more than one script, such that as long as any of the prescriptions within the medication group of interest (A, B, C) are within one year of each other, the condition is met. I have added a new example above illustrating this for patient_id 7. Please let me know if I can provide any other clarification. Thank you very much for your help. 

[novinosrin · Accepted solution]

Hi @wj2  I/We must thank @PaigeMiller for the curiosity. The coding is rather simple but curiosity isn't-

data have;
input Patient_ID	Medication_name $	Start_date :date11.;
format Start_date date9.;
cards;
1	A	1-Jan-11
2	B	1-Mar-15
2	B	1-Apr-15
2	B	1-May-15
2	B	1-Jun-15
3	C	15-Feb-16
3	C	1-Jan-18
3	C	1-Feb-18
4	B	15-May-12
4	B	1-Oct-13
4	B	1-Jan-15
5	A	1-May-14
5	A	1-Jun-14
5	A	20-Jul-14
5	A	1-Jun-16
5	A	1-Jul-16
6	C	25-Jul-18
6	C	25-Jul-18
7   A   01Aug2019
7   B   01Sep2019
;

data want;
 _iorc_=0;
 do _n=1 by 1 until(last.patient_id);
  set have;
  by patient_id;
  if first.patient_id or yrdif(_n_,start_date,'act/act')>1 then do;
   _n_=start_date;
   _iorc_=1;
  end;
  else if yrdif(_n_,start_date,'act/act')>0 then _iorc_+1;
  if _iorc_>=2 and not Index_date then Index_date=_n_;
 end;
 do _n=1 to _n;
  set have;
  if Index_date then output;
 end;
 format index_Date date9.;
 drop _n;
run;