Notice: On March 31, it was announced that Statalist is moving from an email list to a forum. The old list will shut down on April 23, and its replacement, statalist.org is already up and running.

# Re: st: identifying most recent occurrence (line) where value of variable==X in stata

 From Nick Cox To statalist@hsphsun2.harvard.edu Subject Re: st: identifying most recent occurrence (line) where value of variable==X in stata Date Sat, 1 Sep 2012 02:26:35 +0100

```I don't understand "line number". If you mean "observation number",
this is fragile as sorting can change it.

If I understand this correctly, you can just apply the same principles.

You go

gen when_eq_to_1 = time if X == 1
bysort id (time) : replace when_eq_to_1 = when_eq_to_1[_n-1] if
missing(when_eq_to_1)

It helps to give a concrete example of what you want. Made-up data are
fine.Word pictures are far more difficult for others to absorb.

Nick

On Fri, Aug 31, 2012 at 11:32 PM, Alison El Ayadi
> Hi Nick,
>
> I'm following up on this because I've realized that this is not quite the solution that I need.  Because the method provides me with the time that the variable last changed, instead of returning the line number where the variable==1, it is giving me the following line number, because in the following line, the variable then returns to 0.  It might be reasonable to just subtract 1 from this, but there is theoretically the possibility that the variable was==1 on two subsequent times.  So instead of the last time that it changed, I need the last time that it was equal to 1 only.  Can I do:
>
>
> Thoughts?
>
> thanks,
> A
>
>>>> Nick Cox  08/29/12 2:31 PM >>>
> See
>
> http://www.stata.com/statalist/archive/2012-08/msg00633.html
>
> and its references.
>
> You appear to want a minor variation on that problem: _when_ did a
> variable last change?
>
> . l
>
>      +---------------+
>      | id   time   y |
>      |---------------|
>   1. |  1      1   3 |
>   2. |  1      2   3 |
>   3. |  1      3   4 |
>   4. |  1      4   4 |
>   5. |  1      5   4 |
>      |---------------|
>   6. |  2      1   6 |
>   7. |  2      2   7 |
>   8. |  2      3   7 |
>   9. |  2      4   7 |
>  10. |  2      5   7 |
>      +---------------+
>
> . bysort id (time) : gen diff = y != y[_n-1]
>
> . by id : gen whenchange = time if diff
> (6 missing values generated)
>
> . by id : replace whenchange = whenchange[_n-1] if missing(whenchange)
>
> . l
>
>      +---------------------------------+
>      | id   time   y   diff   whench~e |
>      |---------------------------------|
>   1. |  1      1   3      1          1 |
>   2. |  1      2   3      0          1 |
>   3. |  1      3   4      1          3 |
>   4. |  1      4   4      0          3 |
>   5. |  1      5   4      0          3 |
>      |---------------------------------|
>   6. |  2      1   6      1          1 |
>   7. |  2      2   7      1          2 |
>   8. |  2      3   7      0          2 |
>   9. |  2      4   7      0          2 |
>  10. |  2      5   7      0          2 |
>      +---------------------------------+
>
> I put no faith in "line numbers": they just vary with sort order
> (unless this is obscure jargon that you are using, such as "ob").
>
> Nick
>
> On Wed, Aug 29, 2012 at 7:10 PM, Alison El Ayadi
>  wrote:
>
>> I am working with a vertical dataset that is essentially tracking blood loss across time for a group of ob patients.  One important variable for cleaning the data is change in drape (which indicates that the cumulative increase should stop).  Because the data is also tracking other variables, this information is not always recorded on the same line that the blood loss is being recorded on.  I would like to generate a variable that tells me the line number of the most recent drape change. but am having trouble figuring that out.  I have used commands in the past such as L. and have seen information online about how to id the first and last occurrences conditionally, but nowhere have I found it possible to condition the skip on the value of the prior variable.  This is necessary because I am not sure how many lines are in between the index line and the line where I would like to extract the data from, and this will vary by line.
>>
>> In a nutshell, I would like each line to read:
>> id, line number, blood loss reported on that line, drape change reported on that line, last line number that drape change was reported (or missing if none prior).
>>
>> Does anyone have any ideas?

*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/statalist/faq
*   http://www.ats.ucla.edu/stat/stata/
```