How to identify patients with four or more asthma prescriptions within a 12 month rolling period.

[Asboaheng]

Hi all, I need your help in selecting patients with four or more asthma prescriptions within a 12 month rolling period. I have the first column as patient ID, the second column as the Drug identification number (DIN), the third column as the date (yyyymmdd). Any help or assistance will be greatly appreciated. Thank you.

[Reeza]

It helps if you provide sample data, fake data is 100% fine, just mock it up in Excel and paste it in if you need.

[Asboaheng]

Thank you. Please find attached the data I have created as requested.

PatientID	Drug ID	Fill Date
1	2244	1998-01-01
2	2235	1998-01-02
3	2276	1998-01-03
2	2266	1998-01-04
2	2244	1998-01-05
1	2233	1998-05-09
1	2276	1998-05-10
1	2255	1998-05-11
2	3344	1998-05-12
2	3344	1998-05-13
3	2345	1998-05-14
3	1234	1999-08-16
3	6677	1999-08-17
3	3344	1999-08-18
4	4444	1999-08-19
4	4444	1999-08-20
4	3333	1999-08-21
5	8899	1999-08-22
5	8899	1999-08-23
6	3344	1999-08-24
6	2244	1999-08-25
7	2789	1999-08-26
8	9809	1999-08-27
8	1234	1999-08-28
8	1234	1999-08-29
8	1245	1999-08-30
9	1389	1999-08-31
9	7870	1999-09-01

[ballardw]

'Same asthma prescription" or "any asthma prescription"? Considerably different problems, especially with the plethora of drug codes out there.

Depending on which version of a drug code you have the same drug may have different code as at least one of the code schemes includes manufacturer information.

[Asboaheng]

Any asthma prescription. It could be the same drug prescribed at different dates within a year or different drugs prescribed to a patients within a year.

[mkeintz]

"12 month rolling period".   So how do you want the rolling 12-month windows in the output?  Do you want an observation for every 12-month window in a patient's history (presumably with some sort of drug count for each window)? 

Or do want an observation only for every 12-month window with 4 or more drugs?   And if the answer is yes, then  do you want one observation for every window that qualifies?  I.e. if a patient uses drugs in Feb 99, Apr 99, Jun 99, Aug 99, then conceivably there are 8 qualifying windows   (from  Jul 1998-Jun 1999    through Feb 1999-Jan 2000).

It would be good to show what the output data would look like for the sample input data you provided.

[Asboaheng]

Yes, I want an observation only for every 12-month window (or within a year) with 4 or more drugs?.

[Reeza]

Do you care what drug? Or do you just need the list of IDs? As indicated by MKeintz, it would be ideal if you could show the expected output from your sample input.