Sum up observation within a group regarding years.

[Jan Schubert]

Hello everybody,
I just started to work with STATA and I need help with my project.
I have a dataset that contains observations of companies between the years 2010 & 2020. I have an ID-Variable, that uniquely identifies every company, a variable "Action" which is filled with the numbers 0 & 1 for 0 if the action didnt took place in this year and 1 if it took place, and the variable year. Due to the fact that the action can happen multiple times a year I dont have one year in one row.
Here you can see an example of my Dataset with company ID's 1234 & 5678:

ID	Year	Action	????
1234	2010	0
1234	2010	1
1234	2010	1
1234	2011	0
1234	2012	0
1234	2012	1
1234	…	…
5678	2010	0
5678	2011	0
5678	2011	1
5678	2012	1
5678	…	…

In the columns "???" I want a new variable that counts the amount of actions for each company for the year X + the next 2 years. In this example it would be for 1234: Sum up actions of 2010 (3) + 2011 (1) + 2012 (2) and write it back in the row of 2010 in "???". The result would be "3" in the rows for 2010. Next for 2011 + 2 Years an so on. Due to the fact that there are multiple rows for a year I cant tell STATA to do it for one row and add the next two, I need to tell it to do it regarding the year within a Company ID and right now I have no clue how I can create such a loop(?).
Is anyone out there with a possible solution?

[Carlo Lazzaro]

Jan;
welcome to this forum.
Do you mean something along the following lines?

. use "https://www.stata-press.com/data/r17/nlswork.dta"
(National Longitudinal Survey of Young Women, 14-24 years old in 1968)

. bysort idcode (year): egen wanted=total(ln_wage) if _n<=3

. bysort idcode (year): replace wanted=. if _n>1

.

[Jan Schubert]

Hello Carlo,
unfortunetly this creates only values for the first row within the group and like I said I can have actions in a year mutliple times so I cant go for rows (_n<=3) because somtimes STATA has to calculation actions of 3x2010 + 2011 + 2x2012 as example so in this case for 2010+ next 2 years it would be 6 rows while for 2011 + 2 next to years it could only be 5 rows or smth. like this. Hard to explain somehow..

[Carlo Lazzaro]

Jan:
you may want to try something along the folllowing lines:

. use "https://www.stata-press.com/data/r17/nlswork.dta"
(National Longitudinal Survey of Young Women, 14-24 years old in 1968)
. bysort idcode (year): egen wanted=sum(tenure) if nev_mar==1

. bysort idcode (year): replace wanted=. if _n>1 & wanted!=.

.

[Jan Schubert]

Hey Carlo,
I am sorry, I wasnt able to use you first code in the past because I made a mistake. I used a part of your answer and it worked, thank you very much!