topic Data cleaning: index dates in SAS Programming

Data cleaning: index dates

wj2 — Wed, 02 Sep 2020 19:54:05 GMT

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

Re: Data cleaning: index dates

PaigeMiller — Wed, 02 Sep 2020 19:38:48 GMT

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?

Re: Data cleaning: index dates

wj2 — Wed, 02 Sep 2020 19:51:09 GMT

@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.

Re: Data cleaning: index dates

novinosrin — Wed, 02 Sep 2020 20:41:13 GMT

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;