# Re: st: RE: looping over bys groups?

 From Jeph Herrin <[email protected]> To [email protected] Subject Re: st: RE: looping over bys groups? Date Fri, 03 Nov 2006 15:30:00 -0500

```Thanks Nick for a heroic effort to penetrate my overly succinct
presentation.

The idea is that when you get a script for a medication, it
is for a certain number of days. Diabetics typically get
drugs for 90 days, but sometimes 30 days or 14 days. If one
drug isn't working, the doc may add a drug, or change the drug.
If they change the drug, then we're looking at a new "index"
drug, where going back to the first drug would then represent
another change. If they add a drug, then the patient is taking
two drugs. Or three drugs. They can add anytime.

So each record is either 1) continue 2) switch 3) add. After
a switch record, "continue" now refers to the new drug, not
the original.

The only way to figure out whether a new drug (not the first
one) is a switch or an add is to "look ahead" and see if they
get the first drug within the number of days that the first
prescription was filled for. So if they get drug A on day 1
for 90 days, and get drug B on day 60 for 90 days, the only
way to know if drug B is an "add" or a "switch" is to look
ahead to day 90 and see if there is a drug A again somewhere there.
Maybe they are going to be taking A & B both, or maybe they
dropped A and started B.

In the patient below, the second record (drug 6) indicates an "add"
because the first drug (drug 1) is filled again within 90 days
(record 3). Record 3 represents a "continue" because it is the
start drug, within the 90 days the start drug was prescribed for.

+--------------------------------------------+
|   scrssn        date   medcat   days   gap |
|--------------------------------------------|
|     1081   16 Feb 00        1     90     . |
|     1081   05 May 00        6     90    79 |
|     1081   06 May 00        1     90     1 |
|     1081   25 Jul 00        2     90    80 |
|     1081   01 Aug 00        5     90     7 |
|     1081   26 Sep 00        1     90    56 |
|     1081   22 Oct 00        5     90    26 |
|     1081   01 Nov 00        2     90    10 |
|     1081   23 Jan 01        1     90    83 |
|     1081   01 Feb 01        2     90     9 |

My attempt so far (expanded into several lines for clarity) looks like:

/* for each drug, get days til next script for same drug   */
bys scrssn medcat (date) : gen dtn=date[_n+1]-date

/* could be a switch after current drug                    */
gen switchable=dtn>days&dtn<.

/* switch if the drug changes and previous script is switchable */
bys scrssn (date) : gen switch=medcat!=medcat[_n-1]&switchable[_n-1]==1

/* add if the drug changes and the previous script is not switchable */

this seems promising, but I don't see a way to fix the errors:

+--------------------------------------------------------------------+
|--------------------------------------------------------------------|
1. |   1081   16 Feb 00        1     90    80          0        0     0 |
2. |   1081   05 May 00        6     90     .          0        0     1 |
3. |   1081   06 May 00        1     90   143          1        0     1 |
4. |   1081   25 Jul 00        2     90    99          1        1     0 |
5. |   1081   01 Aug 00        5     90    82          0        1     0 |
6. |   1081   26 Sep 00        1     90   119          1        0     1 |
7. |   1081   22 Oct 00        5     90     .          0        1     0 |
8. |   1081   01 Nov 00        2     90    92          1        0     1 |
9. |   1081   23 Jan 01        1     90     .          0        1     0 |
10. |   1081   01 Feb 01        2     90     .          0        0     1 |

Ignoring obs 3 which should not be a switch or an add, we find that obs 4
is a switch as desired but obs 5 which should be an add is a switch.

And so on.

Hope this makes more sense.

Jeph

Nick Cox wrote:
```
```I only have understood part of this. I don't understand any of
the stuff about number of days at all. Perhaps there are some U.S.
specific details here which you are assuming understood.

The categories, with an arbitrary code for discussion,
seem to include first (1) add (2) switch (3).

Start at the "first"s.

bysort scrssn (date) : gen byte code = 1 if _n == 1

At this point everything else in -code- is missing.

That leaves the "add"s, which are later but the same
as the first.

by scrssn : replace code = 2 if missing(code) and code==code[1]

Then everything else is "switch":

replace code = 3 if missing(code)

I hope this helps a bit.

Nick
[email protected]

Jeph Herrin

```
```This has me tearing my (sparse enough) hair out, thought
I'd share that experience with others...

I have prescription data on patients (-id-); once they start on a
diabetic medication (-medcat-), they can either stay on it, switch, or
add a new one. For each prescription, there is the number of -days-
the script is good for. Here is one patient, where I have
also calculated
the -gap- in days between each fill date:

+--------------------------------------------+
|   scrssn        date   medcat   days   gap |
|--------------------------------------------|
|     1081   16 Feb 00        1     90     . |
|     1081   05 May 00        6     90    79 |
|     1081   06 May 00        1     90     1 |
|     1081   25 Jul 00        2     90    80 |
|     1081   01 Aug 00        5     90     7 |
|     1081   26 Sep 00        1     90    56 |
|     1081   22 Oct 00        5     90    26 |
|     1081   01 Nov 00        2     90    10 |
|     1081   23 Jan 01        1     90    83 |
|     1081   01 Feb 01        2     90     9 |

So this patient started on medcat=1, for 90 days, but after 79 days
got a different med, medcat=2, but refilled the original medcat=1
on day 80. This means that the second record indicates an "add"
instead of a "switch". However, the next time they got their start
med was on 26 Sep 2000, 80+7+56 = 143 days after 6 May, so the
med on 25 Jul 2000 represents "switch" to medcat=2, and the med
on 26 Sep 2000 represents a switch back to the original med.

What I need to do is operationalize this logic, and mark every
record that is not the index med (first medication) as either
a switch, an add, or a continuation. I have written piles of
useless code for this today, and imagine I am missing some
very useful insight/adofile/nerve that would do the trick. Rather
than paste in the broken bits here, I thought I'd leave it open

Other info:
it is just chance that all of the -days- here are 90; many patients
also have 14 & 30 day scripts)
there are only 6 distinct -medcat- medication categories, so they
could be handled one at a time
as long as the last record is within the -days- covered by the last
switched or continued medcat, it is considered an -add-

I keep thinking the solution is of the form

bys scrssn (date) : <loop over 1/_N for each record>

but can't see a way to do it.
```
```*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/

```
```*
*   For searches and help try:
*   http://www.stata.com/support/faqs/res/findit.html
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```