sequence drug use over time - data management problem

[Andrea Niles]

Hi Everyone,

I have a data management problem that I am struggling with. I have a longitudinal dataset with 10 timepoints in which a person can go on and off a medication. I would like to create a new variable that indicates medication phase - i.e. takes one value for "before medication," another value for "first medication," another value for "after first medication", another value for "second medication" and a final value for "after second medication." So the data are set up as below, and I want a new variable called drug status, that for this example, would be 1,1,2,2,3,4,5,5,5,5. Any ideas would be greatly appreciated! Thanks!

id	time	drug
1	1	0
1	2	0
1	3	1
1	4	1
1	5	0
1	6	1
1	7	0
1	8	0
1	9	0
1	10	0

Andrea

[Clyde Schechter]

I don't understand your description of what you want, but the following code produces the sequence of values for the new variable (called wanted in the code below) that you show:

* Example generated by -dataex-. To install: ssc install dataex
clear
input byte(id time drug)
1  1 0
1  2 0
1  3 1
1  4 1
1  5 0
1  6 1
1  7 0
1  8 0
1  9 0
1 10 0
end

by id (time), sort: gen wanted = sum(drug != drug[_n-1])

In the future, when showing data examples, please use the -dataex- command to do so, as I have done here. If you are running version 15.1 or a fully updated version 14.2, it is already part of your official Stata installation. If not, run -ssc install dataex- to get it. Either way, run -help dataex- to read the simple instructions for using it. -dataex- will save you time; it is easier and quicker than typing out tables. It includes complete information about aspects of the data that are often critical to answering your question but cannot be seen from tabular displays or screenshots. It also makes it possible for those who want to help you to create a faithful representation of your example to try out their code, which in turn makes it more likely that their answer will actually work in your data.



When asking for help with code, always show example data. When showing example data, always use -dataex-.

[Nick Cox]

Here is one way to approach it. I used tsspell from SSC.

clear
input id    time    drug
1    1    0
1    2    0
1    3    1
1    4    1
1    5    0
1    6    1
1    7    0
1    8    0
1    9    0
1    10    0
end

tsset id time

tsspell, pcond(drug)

by id: gen wanted = 1 if sum(drug) == 0
by id: replace wanted = 2 if _spell == 1
by id: egen end1 = max(cond(_spell == 1, time, .))
by id: replace wanted = 3 if time > end1 & drug == 0
by id: replace wanted = 4 if _spell == 2
by id: egen end2 = max(cond(_spell == 2, time, .))
by id: replace wanted = 5 if time > end2
drop end1 end2 _seq _end

list, sepby(id _spell)

     +------------------------------------+
     | id   time   drug   _spell   wanted |
     |------------------------------------|
  1. |  1      1      0        0        1 |
  2. |  1      2      0        0        1 |
     |------------------------------------|
  3. |  1      3      1        1        2 |
  4. |  1      4      1        1        2 |
     |------------------------------------|
  5. |  1      5      0        0        3 |
     |------------------------------------|
  6. |  1      6      1        2        4 |
     |------------------------------------|
  7. |  1      7      0        0        5 |
  8. |  1      8      0        0        5 |
  9. |  1      9      0        0        5 |
 10. |  1     10      0        0        5 |
     +------------------------------------+

Note that Clyde's neat solution hinges on there being just 2 spells of treatment. If there are more, then it just needs

replace wanted = 5 if wanted > 5

to lump together all times after the second spell.