Select first value

[Kim Vaarts]

I have data in long format. I want to select the first value of the variable "days" which is <=300 for each subject. In the example below I want to select for subject/id1 50 days; for subject/id 2 none (because days>300) and for subject/id3 50 days. How do I do that for a whole dataset? Please help.

id days
1 50
1 100
1 600
2 1000
3 70
3 50

[Clyde Schechter]

* Example generated by -dataex-. For more info, type help dataex
clear
input byte id int days
1   50
1  100
1  600
2 1000
3   70
3   50
end

sort id, stable
by id: gen runner = sum(days <= 300)
by id: egen wanted = max(cond(runner == 1, days, .))
drop runner

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 16 or later, or a fully updated version 15.1 or 14.2, -dataex- 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.

[Kim Vaarts]

Dear @Clyde Schechter thank you. But now I get for subject 1 50 days in the whole long format. How do I only keep the 50 days in row 1 for the first subject, 50 days in row 2 for the third subject?

[Leonardo Guizzetti]

I interpreted your question differently from Clyde.

clear
input byte id int days
1   50
1  100
1  600
2 1000
3   70
3   50
end

bysort id (days): gen want = cond(_n==1 & days <= 300, 1, 0)
keep if want
drop want

Or more succinctly:

bysort id (days): keep if _n==1 & days <= 300

This says, for each value if ID, with day sorted in ascending order, keep only the first record that is <= 300 days.

Edit: A note on -sort, stable-. Some regard the stable option as potentially very dangerous because it maintains all records in their existing order beyond whatever is used as the sort key. This means that if your dataset were sorted differently, and then resorted using the stable option, you would arrive a (potentially) entirely different sort order. It is better to have a dataset structure whose order can be completely determined by the sort key and not have to rely on a stable option to avoid silent and difficult-to-troubleshoot ambiguous sorting later on.

[Clyde Schechter]

Re #3, from your description of how you want the third subject in your example handled, I infer that you do not actually want what my code does. Rather, you want what Leonardo Guizzetti proposes in #4. So I suggest you use his code.

The difference between them is that in my code I interpreted "First" to mean the first in the order in which they appear in the example data. Leonardo Guizzetti's code assumes that by "first" you really mean smallest. By stating in #3 that your desired result for id 3 is 50, you disambiguated the situation in favor of smallest rather than first in order of appearance.