Obsidian | Level 7

## How to compute binary variable indicating treatment status by overlapping dates

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.

Super User

## Re: How to compute binary variable indicating treatment status by overlapping dates

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.

Discussion stats