annualized average volume calculation

[Muhammad Rashid]

Dear Stata users,

Please can someone help me to come up with a syntax to calculate the average annual volume of procedure (ici) per operator (opid)? its panel data over 10 years and it is possible that volume of procedure changes over time per operator. I have pasted a copy of the data below. I have gone through the previous posts relevant to this topic but nothing seems to work for me. thanks

[CODE]
* Example generated by -dataex-. For more info, type help dataex
clear
input int(opid year) float ici
652 2014 1
336 2014 1
134 2014 1
199 2014 1
21 2014 1
796 2014 1
828 2014 1
134 2014 1
423 2014 1
808 2014 1
797 2014 1
490 2014 1
555 2014 1
21 2014 1
219 2014 1
447 2014 1
305 2014 1
427 2014 1
263 2014 1
215 2014 1
308 2014 1
409 2014 1
796 2014 1
727 2014 1
375 2014 1
218 2014 1
360 2014 1
755 2014 1
600 2014 1
520 2014 1
50 2014 1
41 2014 1
50 2014 1
401 2014 1
401 2014 1
530 2014 1
132 2014 1
303 2014 1
532 2014 1
211 2014 1
62 2014 1
250 2014 1
796 2014 1
795 2014 1
136 2014 1
298 2014 1
215 2014 1
635 2014 1
405 2014 1
240 2014 1
825 2014 1
253 2014 1
336 2014 1
116 2014 1
349 2014 1
495 2014 1
401 2014 1
204 2014 1
155 2014 1
532 2014 1
310 2014 1
132 2014 1
310 2014 1
796 2014 1
598 2014 1
790 2014 1
722 2014 1
116 2014 1
622 2014 1
400 2014 1
192 2014 1
146 2014 1
204 2014 1
427 2014 1
401 2014 1
532 2014 1
532 2014 1
298 2014 1
622 2014 1
734 2014 1
555 2014 1
353 2014 1
116 2014 1
108 2014 1
570 2014 1
116 2014 1
219 2014 1
121 2014 1
38 2014 1
709 2014 1
305 2014 1
445 2014 1
298 2014 1
21 2014 1
360 2014 1
692 2014 1
790 2014 1
310 2014 1
293 2014 1
230 2014 1

[Clyde Schechter]

by opid (year), sort: egen wanted = mean(ici)

[Muhammad Rashid]

Thanks, Clyde, but I think it's slightly more complicated than this, I paste another copy of the data, If you look at opid 15, its appears more than once each year. I want to create a variable which will give me an average per year of each opid based on their ici- I mean average number of ici performed by each opid per year.

opid year ici wanted
2 2021 1 .1
6 2019 1 .008547
7 2021 1 .0005288
8 2015 1 .004644
8 2015 1 .004644
8 2016 1 .004644
8 2016 1 .004644
8 2017 1 .004644
8 2018 1 .004644
8 2019 1 .004644
8 2020 1 .004644
8 2021 1 .004644
9 2015 1 .0053071
9 2015 1 .0053071
9 2016 1 .0053071
9 2016 1 .0053071
9 2016 1 .0053071
9 2021 1 .0053071
9 2021 1 .0053071
10 2021 1 .0011312
11 2014 1 .0305344
11 2014 1 .0305344
11 2014 1 .0305344
11 2015 1 .0305344
11 2015 1 .0305344
11 2015 1 .0305344
11 2016 1 .0305344
11 2016 1 .0305344
11 2017 1 .0305344
11 2017 1 .0305344
11 2017 1 .0305344
11 2018 1 .0305344
13 2019 1 .1137725
13 2019 1 .1137725
13 2019 1 .1137725
13 2019 1 .1137725
13 2019 1 .1137725
13 2019 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2020 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
13 2021 1 .1137725
15 2015 1 .0272692
15 2015 1 .0272692
15 2015 1 .0272692
15 2016 1 .0272692
15 2016 1 .0272692
15 2016 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2017 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2018 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2019 1 .0272692
15 2020 1 .0272692
15 2020 1 .0272692
15 2020 1 .0272692
15 2020 1 .0272692
15 2020 1 .0272692
15 2021 1 .0272692

[Clyde Schechter]

OK, I'm confused about what you want. It is a contradiction in terms to say "an average per year of each opid based on their ici- I mean average number of ici performed by each opid per year." If you want average number of ici per year (wanted1), that would necessarily be a single number for each opid which counts up the number of ici's for the opid in each year and then averages those across the years.

If you want the number of ici each opid provides in each year (wanted2), that is easily done as well, but it is a different matter.

collapse (sum) wanted2 = ici, by(opid year)
by opid (year): egen wanted1 = mean(wanted2)

list, noobs clean

If you have something else in mind, I suggest you post back with some examples showing the results you want (that you calculate by hand) and explaining how you arrived at those results.

[Muhammad Rashid]

Thanks, Clyde, this is very helpful and sorry I couldn't make it clear. but its "wanted2" I need, the only thing is that given I have collapsed the data ( over 25 thousand observations) to almost 2500 now, how can I link it back to master data as I need to divide the opid into quartiles based on their "wanted2" and analyse the full 25 thousand observations for many other covariates. I am very grateful, your help is tremendous

[Clyde Schechter]

Well, rather than put them back together, we can get wanted2 in the original data without collapse. So forget the code in #4 and do this instead:

by opid year, sort: egen wanted2 = total(ici)

[Muhammad Rashid]

Thank you, I thought "sum" may work but you are right it's (total) the same thing. very helpful indeed. Cheers