topic Re: finding index date and intervals in New SAS User

finding index date and intervals

Banke — Wed, 28 Sep 2022 15:16:32 GMT

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;

Re: finding index date and intervals

PaigeMiller — Wed, 28 Sep 2022 14:04:35 GMT

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.

Re: finding index date and intervals

Banke — Wed, 28 Sep 2022 15:40:05 GMT

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

Re: finding index date and intervals

PaigeMiller — Wed, 28 Sep 2022 15:42:34 GMT

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;

Re: finding index date and intervals

Banke — Wed, 28 Sep 2022 15:48:39 GMT

Thats what i did, thank you so much!

Re: finding index date and intervals

Banke — Wed, 28 Sep 2022 16:02:26 GMT

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;