Hi All,

I am having trouble creating another observation under one variable which will be an average of a number of other observations for each year. My data is attached below, but I am trying to take the average of a group of products's "percent" for each year and then create a new set of observations for this new averaged product. If the products are:

Code:
inlist(prod, "Carbonates", "Energy Drinks" , "Concentrates" ,"Juice", "RTD Tea", "Sports Drinks")
Then I would like to take the average of the "percent" column values for all of these products and make a new product called "Tax drinks." The average will be different for each year, based on what the product percentages are equal to above for that given year. For example, in 2016, I want the percent value of each of the products above (Carbonates, etc.) averaged together in 2016 and then for that averaged value to be the percent value for the new prod "Tax drinks." Does anyone have an ideas on how I may be able to accomplish this? Thank you so much for any and all suggestions.

Many thanks,
Jim

Code:
* Example generated by -dataex-. To install: ssc install dataex
clear
input str14 country str43 prod int year byte type float percent
"Ireland" "Baby Food"         2014 0    88.76344
"Ireland" "Baby Food"         2015 0    88.85345
"Ireland" "Baby Food"         2016 0    88.91432
"Ireland" "Baby Food"         2017 0    88.97702
"Ireland" "Baby Food"         2018 0    89.05828
"Ireland" "Baby Food"         2019 0    89.15837
"Ireland" "Baby Food"         2020 0    89.23181
"Ireland" "Baby Food"         2014 1    11.23656
"Ireland" "Baby Food"         2015 1   11.146546
"Ireland" "Baby Food"         2016 1   11.085676
"Ireland" "Baby Food"         2017 1   11.022975
"Ireland" "Baby Food"         2018 1   10.941716
"Ireland" "Baby Food"         2019 1    10.84163
"Ireland" "Baby Food"         2020 1   10.768182
"Ireland" "Baked Goods"       2014 0    96.40752
"Ireland" "Baked Goods"       2015 0    96.42551
"Ireland" "Baked Goods"       2016 0    96.42312
"Ireland" "Baked Goods"       2017 0    96.40865
"Ireland" "Baked Goods"       2018 0     96.3924
"Ireland" "Baked Goods"       2019 0    96.44029
"Ireland" "Baked Goods"       2020 0    96.34309
"Ireland" "Baked Goods"       2014 1     3.59136
"Ireland" "Baked Goods"       2015 1   3.5733924
"Ireland" "Baked Goods"       2016 1    3.575801
"Ireland" "Baked Goods"       2017 1    3.590291
"Ireland" "Baked Goods"       2018 1   3.6065545
"Ireland" "Baked Goods"       2019 1    3.558706
"Ireland" "Baked Goods"       2020 1    3.655866
"Ireland" "Baked Goods"       2014 2 .0011187661
"Ireland" "Baked Goods"       2015 2 .0011032855
"Ireland" "Baked Goods"       2016 2 .0010741096
"Ireland" "Baked Goods"       2017 2 .0010554073
"Ireland" "Baked Goods"       2018 2 .0010359032
"Ireland" "Baked Goods"       2019 2 .0010000111
"Ireland" "Baked Goods"       2020 2 .0010402437
"Ireland" "Bottled Water"     2014 0    99.74924
"Ireland" "Bottled Water"     2015 0    99.75259
"Ireland" "Bottled Water"     2016 0     99.7564
"Ireland" "Bottled Water"     2017 0    99.76518
"Ireland" "Bottled Water"     2018 0    99.76906
"Ireland" "Bottled Water"     2019 0     99.7739
"Ireland" "Bottled Water"     2020 0    99.78398
"Ireland" "Bottled Water"     2014 1   .24856895
"Ireland" "Bottled Water"     2015 1       .2453
"Ireland" "Bottled Water"     2016 1    .2415012
"Ireland" "Bottled Water"     2017 1   .23278593
"Ireland" "Bottled Water"     2018 1    .2289391
"Ireland" "Bottled Water"     2019 1    .2241746
"Ireland" "Bottled Water"     2020 1    .2141973
"Ireland" "Bottled Water"     2014 2 .0021854744
"Ireland" "Bottled Water"     2015 2    .0021185
"Ireland" "Bottled Water"     2016 2 .0020976719
"Ireland" "Bottled Water"     2017 2 .0020299621
"Ireland" "Bottled Water"     2018 2 .0019979784
"Ireland" "Bottled Water"     2019 2 .0019317467
"Ireland" "Bottled Water"     2020 2 .0018214793
"Ireland" "Breakfast Cereals" 2014 0    88.36055
"Ireland" "Breakfast Cereals" 2015 0    88.84888
"Ireland" "Breakfast Cereals" 2016 0    89.38763
"Ireland" "Breakfast Cereals" 2017 0    89.83304
"Ireland" "Breakfast Cereals" 2018 0    89.93861
"Ireland" "Breakfast Cereals" 2019 0    90.04226
"Ireland" "Breakfast Cereals" 2020 0    90.45103
"Ireland" "Breakfast Cereals" 2014 1   11.639448
"Ireland" "Breakfast Cereals" 2015 1   11.151115
"Ireland" "Breakfast Cereals" 2016 1    10.61238
"Ireland" "Breakfast Cereals" 2017 1    10.16696
"Ireland" "Breakfast Cereals" 2018 1   10.061397
"Ireland" "Breakfast Cereals" 2019 1     9.95774
"Ireland" "Breakfast Cereals" 2020 1    9.548981
"Ireland" "Carbonates"        2014 0    93.63103
"Ireland" "Carbonates"        2015 0    93.62852
"Ireland" "Carbonates"        2016 0    93.62292
"Ireland" "Carbonates"        2017 0    93.60877
"Ireland" "Carbonates"        2018 0    93.58997
"Ireland" "Carbonates"        2019 0    93.56568
"Ireland" "Carbonates"        2020 0    93.57092
"Ireland" "Carbonates"        2014 1    6.348202
"Ireland" "Carbonates"        2015 1    6.350781
"Ireland" "Carbonates"        2016 1     6.35649
"Ireland" "Carbonates"        2017 1    6.370764
"Ireland" "Carbonates"        2018 1    6.389736
"Ireland" "Carbonates"        2019 1    6.414242
"Ireland" "Carbonates"        2020 1    6.408684
"Ireland" "Carbonates"        2014 2    .0207635
"Ireland" "Carbonates"        2015 2   .02070044
"Ireland" "Carbonates"        2016 2  .020589443
"Ireland" "Carbonates"        2017 2   .02046161
"Ireland" "Carbonates"        2018 2   .02030443
"Ireland" "Carbonates"        2019 2  .020074073
"Ireland" "Carbonates"        2020 2   .02039159
"Ireland" "Coffee"            2014 0         100
"Ireland" "Coffee"            2015 0         100
"Ireland" "Coffee"            2016 0         100
"Ireland" "Coffee"            2017 0         100
"Ireland" "Coffee"            2018 0         100
"Ireland" "Coffee"            2019 0         100
"Ireland" "Coffee"            2020 0         100
"Ireland" "Concentrates"      2014 0    64.63536
"Ireland" "Concentrates"      2015 0    64.63651
end
label values type Type
label def Type 0 "Control", modify
label def Type 1 "Sugars", modify
label def Type 2 "Artificial sugars", modify