Keep first observation (with example dataex)

[Kim Vaarts]

I have data in long format. See below example.

I want to select the first observation of the variable "days" for each id where days<=300 & percent<=10 & non missing. For subjects that do not have that condition I want 0 (e.g. subject 2).

Just like the "wanted" variable, where wanted = 1 for days<=300 & non missing & Percent<=10, for the first observation per id.

Which code do I run for the whole dataset? Please help. Thanks in advance.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int days float Percent byte wanted
1   50  9 1
1  100  8 .
1  500 12 .
2 2000  4 0
2 4000 40 .
2 6000 .7 .
3  100  5 1
3  150  4 .
4    .  . .
4  200 .1 0
end

@Leonardo Guizzetti can you please help with this example? This is is clearer example. Thank you.

[Kim Vaarts]

@Andrew Musau can you help please?

[Leonardo Guizzetti]

capture drop wanted
bysort id (days) : gen byte wanted = cond(_n==1 & days<=300 & Percent<=10, 1, 0)

This is now your 3rd such post with very similar questions where you ask for code but do not show your attempt to solve the problem yourself. I gently suggest you spend some time with the Stata documentation and getting to learn the language a little more.

[Andrew Musau]

From the example in #1, it appears that you want to preserve the original order of observations in the dataset; otherwise, Leonardo’s code sorts by days and produces a different result for id = 4. If this is the case and you do not have an ordering variable, then you should create one before creating the desired variable.

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int days float Percent byte wanted
1   50  9 1
1  100  8 .
1  500 12 .
2 2000  4 0
2 4000 40 .
2 6000 .7 .
3  100  5 1
3  150  4 .
4    .  . .
4  200 .1 0
end

gen obsno=_n
bys id (obsno): gen Wanted= sum(days<=300 & Percent<=10 & _n==1) if sum(sum(!missing(days)))==1
drop obsno

Res.:

. l, sepby(id)

     +---------------------------------------+
     | id   days   Percent   wanted   Wanted |
     |---------------------------------------|
  1. |  1     50         9        1        1 |
  2. |  1    100         8        .        . |
  3. |  1    500        12        .        . |
     |---------------------------------------|
  4. |  2   2000         4        0        0 |
  5. |  2   4000        40        .        . |
  6. |  2   6000        .7        .        . |
     |---------------------------------------|
  7. |  3    100         5        1        1 |
  8. |  3    150         4        .        . |
     |---------------------------------------|
  9. |  4      .         .        .        . |
 10. |  4    200        .1        0        0 |
     +---------------------------------------+