finding index date and intervals

[Banke]

Hello everyone,

I want to find continuous eligibility of certain patients with insurance. Each patient appear multiple times with different prescription dates (variable = fill_date). As a first step, i want to find the first date they filled the prescription (index date) and 180 days before and after that date. The date format is MMDDYY10. and I would like to convert to numeric so that i can subtract and add 180 from this. I have attached the code. I have to find the first prescription date for each patient. I would appreciate if you please help me verify my code and how I can convert the date

/**what i have***/

patient ID    fill date

patient 1      02/23/2001

patient 1      02/25/2001

patient 1      03/14/2003

patient 2       01/01/2006

patient 2      10/14/2001

patient 2       08/25/2008

patient 3       07/14/2004

/**what i want**/

patient ID    fill date           fill_date in days   start date (+180)   end date (-180)

patient 1      02/23/2001         01234                      1054                    1414

patient 2      10/14/2001          05555                      5375                    5735

patient 3      07/14/2004          33333                    33153                    33513

Thank you

PROC SORT DATA = XX;
BY PATIENT_ID FILL_DATE DESCENDING FILL_DATE;
RUN;

/** IDENTIFYING FIRST.FILL DATE**/
DATA INDEX_MONTH;
DO UNTIL (LAST.PATIENT_ID);
SET XX;
BY PATIENT_ID FILL_DATE;
IF FIRST.PATIENT_ID THEN INDEX_DT = FILL_DATE;
END;
RUN;

/**check**/
PROC PRINT DATA = MYHUMANA.DXMED (OBS = 100);
VAR PATIENT_ID FILL_DATE GENERIC_DRUG_NAME NDC;  
RUN;

[PaigeMiller · Accepted solution]

The date format is MMDDYY10. and I would like to convert to numeric so that i can subtract and add 180 from this.

Do you mean that the format as seen by SAS (in PROC CONTENTS or elsewhere) is MMDDYY10. ? Or do you mean that it looks to you like MMDDYY10. before you read in the data, but it shows up as character in SAS?

If the date format is MMDDYY10. in SAS (in PROC CONTENTS or elsewhere), then the variable is numeric (so you don't need to convert it), and you can add or subtract 180 by adding or subtracting. Example:

start_date_plus_180 = fill_date + 180;

Please do not provide data as Excel files. Many of us will not download Excel files, they can be a security threat. The proper way to provide data is via working SAS data step code which you can type in yourself or use these instructions.

[Banke]

Ok, thank you. I have removed the attachment, its a dummy sample but thank you for showing me the right way.The date format as seen by SAS is MMDDYY10. (informat is DATETIME20.). It appears as such too. Using your code gave me the following, thank you.
patient ID fill date start date (+180) end date (-180)

patient 1 04/25/2015 20023 20383

patient 2 12/13/2016 20621 20981

patient 3 12/30/2016 20638 20998

Can you please help me check if my initial step of finding the first fill_date for each patient. Thank you

[PaigeMiller]

To find the first date for each patient, you have to sort by patient and date (if it is not already sorted) and then

data first_date;
    set have;
    by patient;
    if first.patient;
run;

[Banke]

Thats what i did, thank you so much!

[Banke]

I have an additional question please, i am trying to identifying the first fill date (first time the patient filled the prescription which will be the oldest date), i had sorted by patient id and date with the descending option  and then output first_id. I am confused as the descending option may sort from the newest prescription date to the oldest, so should i output last.id instead? Thank you

PROC SORT DATA = MEDICATION;
BY PATIENT_ID FILL_DATE DESCENDING FILL_DATE;
RUN;

DATA INDEX_DATE;
DO UNTIL (LAST.PATIENT_ID);
SET MEDICATION;
BY PATIENT_ID FILL_DATE ;
IF FIRST.PATIENT_ID THEN INDEX_DT = FILL_DATE;
END;
RUN;