Hi,

I have data on project teams for a number of different companies. Included in the data is a DATE variable, which contains the completion date of the project and an acquisition_effect dummy, which indicates whether the project was completed within 365 days of the acquisition of the company. The TeamComp variable describes the share of a specific position (such as programmer, for example) within the project team.

clear
input str46 firm_id float(DATE acquisition_effect TeamComp)
"amaze-entertainment-inc" 16710 0 .3095238
"amaze-entertainment-inc" 16710 0 .23404256
"amaze-entertainment-inc" 16710 0 .3125
"amaze-entertainment-inc" 16741 0 .1025641
"amaze-entertainment-inc" 16861 0 .2962963
"amaze-entertainment-inc" 16922 0 .3902439
"amaze-entertainment-inc" 16953 0 .2121212
"amaze-entertainment-inc" 16953 0 .23076923
"amaze-entertainment-inc" 17045 0 .4038461
"amaze-entertainment-inc" 17045 0 .35
"amaze-entertainment-inc" 17075 0 .13492064
"amaze-entertainment-inc" 17075 0 .2173913
"amaze-entertainment-inc" 17106 0 .1904762
"amaze-entertainment-inc" 17106 0 .1
"amaze-entertainment-inc" 17106 0 .15384616
"amaze-entertainment-inc" 17106 0 .2769231
"amaze-entertainment-inc" 17106 0 .14634146
"amaze-entertainment-inc" 17106 0 .1
"amaze-entertainment-inc" 17226 1 .12068965
"amaze-entertainment-inc" 17287 1 .06909091
"amaze-entertainment-inc" 17287 1 .1521739
"amaze-entertainment-inc" 17440 1 .2244898
"amaze-entertainment-inc" 17440 1 .2077922
"amaze-entertainment-inc" 17440 1 .0754717
"amaze-entertainment-inc" 17440 1 .12
"amaze-entertainment-inc" 17440 1 .203125
"amaze-entertainment-inc" 17471 1 .24242425
"amaze-entertainment-inc" 17684 0 .1521739
"amaze-entertainment-inc" 17806 0 .13043478
"amaze-entertainment-inc" 18049 0 .22222222
"amaze-entertainment-inc" 18718 0 .23913044
end
format %td DATE

Now, I'm trying to group this data into 12 Month periods, but only group observations with the same acquisition_effect dummy. My goal is to calculate the mean TeamComp in those periods.

So basically, I need an ID that is 1 for all observations within 12 Months of the first observation if sorted by firm_id and DATE, then 2 for the first observation outside of those 12 months and for all observations in the subsequent 12 Months, and so on. Observations with the acquisition_effect dummy = 1 need to be grouped separately from the other observations.

So basically it should look something like this:
Firm acquisition-effect Date Group_ID Mean_TeamComp
A 0 04/2011 1 0.5
A 0 08/2011 1 0.5
A 0 06/2014 2 0.7
A 1 07/2014 3 0.2
A 1 08/2014 3 0.2
A 1 10/2015 4 0.8
A 0 11/2015 5 0.9

I've been looking for similar problems here in the forum, but I couldn't really find much on how to create groups based on date ranges. I've found lots of advice on how to create IDs for dates in a specific range (such as between 05/2014 and 08/2014), but since my range is 12 months after the first observation in a group, this didn't really help me out. As I'll be running into similar problems with my project, I'd appreciate if you could also post a link to helpful threads in the forum. Maybe (since I'm still a stata noob), I'm not finding anything because I can't translate my problem into the proper wording.

Thank you so much in advance!

Best,
Julian