How to compute binary variable indicating treatment status by overlapping dates

[ha33]

Hi everyone,

So I have two datasets, one contains unique IDs, prescriptions and their duration by a start date and end date: 

Table 1

ID	ATC	P_DATE	LAST_DATE
1	AA10	03_01_12	03_02_12
1	AA10	03_01_12	03_02_12
1	AA10	03_01_12	03_02_12
2	AA10	14_02_12	14_03_12
2	AA10	15_04_12	15_06_12
2	BB10	12_01_12	12_02_12
2	BB10	15_06_12	15_07_12
3	CC10	27_08_13	27_09_13
3	CC10	12_09_13	12_10_13

In another table I have the IDs and start/end dates of months in relation to an intervention.

Table2

ID	month_num	month_num_start	month_num_end	iv
1	-2	01_02_12	01_03_12	0
1	-1	01_03_12	01_04_12	0
1	1	01_04_12	01_05_12	1
1	2	01_05_12	01_06_12	1
2	-2	15_12_14	15_01_15	0
2	-1	15_01_15	15_02_15	0
2	1	15_02_15	15_03_15	1

I would like to define binary variables (treat_atc) in table 2 that specifies whether the individual was covered by a prescription of the respective drug in that month.

Coverage could be defined as >50% of the month being covered by a prescription. So if the individual redeemed a prescription that supplied 30 days starting on 01_03_2012 (P_date) and the month_num_start of a month_num is 14_03_2012, the treat_atc for that drug would be "1" because the individual is covered 31-14=17 days ~55% of that month. 

I have been playing around with proc sql and different joins but I haven't cracked the code yet. 

Feel free to suggest a different approach.

[ballardw]

What does "respective drug" mean? How do we recognize it given the variables you show?

Does that month_num variable have any role in this? I can't tell what it might be. Bits that aren't needed should be excluded to reduce possible confusion.

In programming the rules are stated as "will be" or  "will not be". Could is right out as it is not a definite rule.

Is "50% of month" a specific number of days or are you going to be fiddling with what an over lap in February may mean (hint: you really don't want to go there especially since you do not seem to be using calendar month boundaries. ) and what would you consider 50% of 31 days when needed??? Since months can have 28, 29, 30 or 31 days you play with a lot of potential divisors with this sort of ratio requirement and I am not going to guess which is used in any specific case.

A suggestion for asking anything related to dates: Do not use 2-digit years. I see 03_01_12 and have to work to tell which is the month, which is the year and which is the day of the month. At least if using 03_01_2012 I only have to worry about one other. I am also a tad concerned that perhaps you don't have SAS date values as NONE of the SAS default date formats will display with underscore characters. Which means the first step would be convert all those values to actual SAS dates values.