Generating a group of a variable based on (by) another variable

[tig som]

In a given year, an organization has multiple trip records. Each trip within an organization in a specific year may be repeated. I have provided the “Trips” for Organization_Year == 1 and Organization_Year == 2 below.


----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input float(Organization_Year Trips)
1  1
1  1
1  1
1  1
1  2
1  3
1  4
1  5
1  6
1  6
1  6
2  7
2  7
2  7
2  8
2  8
2  8
2  9
2  9
2 10
2 10
2 10
2 10
end

------------------ copy up to and including the previous line ------------------

Listed 23 out of 23 observations



I want to generate a new variable called "New_Trip" similar to the "Trip" variable. However, I want the "New_Trip" variable to reset to 1 whenever a new organization_year begins.

I am currently using the following STATA command to generate the desired "New_Trip" variable.



egen New_Trip1 = group(Trip) if Organization_Year==1
egen New_Trip2 = group(Trip) if Organization_Year==2


gen New_Trip = New_Trip1

replace New_Trip=New_Trip2 if New_Trip1==.


However, this approach becomes overwhelming since I have thousands of organization_years in my dataset.

Is there another practical and more efficient solution available?

Please find an example of my "New_Trip" variable and all other variables below, which will clearly explain what I aim to accomplish.


----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. For more info, type help dataex
clear
input float(Organization_Year Trips New_Trip1 New_Trip2 New_Trip)
1  1 1 . 1
1  1 1 . 1
1  1 1 . 1
1  1 1 . 1
1  2 2 . 2
1  3 3 . 3
1  4 4 . 4
1  5 5 . 5
1  6 6 . 6
1  6 6 . 6
1  6 6 . 6
2  7 . 1 1
2  7 . 1 1
2  7 . 1 1
2  8 . 2 2
2  8 . 2 2
2  8 . 2 2
2  9 . 3 3
2  9 . 3 3
2 10 . 4 4
2 10 . 4 4
2 10 . 4 4
2 10 . 4 4
end

------------------ copy up to and including the previous line ------------------

Listed 23 out of 23 observations

Kindly note that I am focused exclusively on generating the "New_Trip" option and do not intend to generate any additional alternatives such as "New_Trip1" or "New_Trip2".

Thank you, and I look forward to your response.

[Nick Cox]

If this is a fresh attempt at https://www.statalist.org/forums/for...-date-variable then that's fine but please say so, both in the first thread and in this one.