SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

Identifying an event in the past with a different start date for each person

Reply
Occasional Contributor
Posts: 18

Identifying an event in the past with a different start date for each person

[ Edited ]

Hi!

 

I need some help with identifying if a patient has a prescription for a drug 6 months before the first fill date for the same drug. What is challenging here is that each patient will have a different first fill date and hence, the 6 month look back period will be different for each patient.

 

For example, for the year 2015 - one patient 1 has their first fill on January 5th while patient 2 has their first fill on March 30. For patient1 I will have to look back to July 5th while for patient 2 I will have to look back upto September 30. 

 

Currently my data is in this format - 

Patient ID                Medication               Fill date                

1                                  Med1                     01/23

1                                  Med2                     5/22

1                                  Med3                       ..

1                                  Med4                       ..

2                                  Med1                       ..

2                                  Med2                       ..

3                                  Med1                       ..

4                                  Med1                       ..                  

4                                  Med2                       ..

 

I can transpose the data and make it wide, but what I am struggling most with is identifying the different 6 month look back period for each patient from the fill date

 

Any help will be much appreciated!

 

Thank you!

Respected Advisor
Posts: 3,777

Re: Identifying an event in the past with a different start date for each person

This is probably a job for INTNX to find the start date of fill-6months.  If you post representative data and desired output you will get better help.

Occasional Contributor
Posts: 18

Re: Identifying an event in the past with a different start date for each person

I already have the start date (Fill date) - I am not sure how to code so that the 6 month look back period is different for each patient. 

Respected Advisor
Posts: 3,777

Re: Identifying an event in the past with a different start date for each person

Look at the documentation for INTNX you will find how to find a date 6 months in the past.

 

If you post some representative data we can show example.

Super User
Posts: 17,912

Re: Identifying an event in the past with a different start date for each person

You don't need to change your data structure. 

 

You use what's called a self join. Join table to itself with the join condition being that the dates are within 6 months given ID and drug match. As mentioned INTNX will calculate the interval, but note that 6 months is not the same duration so you may want to define a fixed number of days rather than 6 months, ie 180 day? 

 

If you post sample input/output data for a code sample make sure to include the case where you have multiple dates for the same med, within the specified duration , ie patient 1 has med1 6 times in 6 months - monthly prescription. 

Super User
Posts: 9,687

Re: Identifying an event in the past with a different start date for each person

Show your sample data and output you want. It looks like SQL , Hash Table both can do that .
Ask a Question
Discussion stats
  • 5 replies
  • 287 views
  • 2 likes
  • 4 in conversation