Frequency and sum of two variables

[Adam Gallaher]

I have a dataset of vehicles on the road. I've created a vehicle ID variable, which is a concatenation of the year, make, model, and sub-model. I've used groups to obtain the 10 most frequency occurring vehicles by year and body style.

groups Make_Model_Year if datayear == 2013 & body_style == "Sedan", order(h) select(10)

However, I realized that I'd like to also know of those 10 most frequently occurring vehicles in my dataset, how many actually exist. There is a second variable "vehicles" which contains integers of total vehicle count for any given vehicle on the road. In this sense, perhaps groups is not the best command to use. Below is an example of the data I am working with. You can see for example that there are technically 8 2004 Jeep Grand Cherokee Limited's and not one in that zip code for 2013. The dataset goes from 2013 to 2020 for reference.

* Example generated by -dataex-. For more info, type help dataex
clear
input str100 Make_Model_Year float vehicles str13 body_style int(datayear zcta)
"2000, FORD, EXPLORER, XLT"                            1 "SUV"           2013 6001
"2004, JEEP, GRAND CHEROKEE, LIMITED"                  8 "SUV"           2013 6001
"1994, FORD, F150, "                                   1 "Pickup Truck"  2013 6001
"2012, LEXUS, RX, 450"                                 2 "SUV"           2013 6001
"2007, NISSAN, PATHFINDER, LE/SE/XE"                   2 "SUV"           2013 6001
"2006, BUICK, LACROSSE, CX"                            3 "Sedan"         2013 6001
"2012, GMC, SIERRA, K2500 SLE"                         0 ""              2013 6001
"1996, FORD, TAURUS, GL"                               2 "Sedan"         2013 6001
"2009, INFINITI, G37, "                                1 "Coupe"         2013 6001
"2009, CHEVROLET, SILVERADO, K2500 HEAVY DUTY LTZ"     1 "Pickup Truck"  2013 6001
"2011, JEEP, WRANGLER UNLIMITED, SAHARA"               1 "SUV"           2013 6001
"2010, FORD, FOCUS, SES"                               1 "Sedan"         2013 6001
"1996, DODGE, RAM 1500, "                              1 "Pickup Truck"  2013 6001
"2012, HONDA, ACCORD, LX"                              1 "Coupe"         2013 6001
"2001, DODGE, RAM 3500, "                              0 ""              2013 6001
"2003, CHRYSLER, VOYAGER, LX"                          1 "Passenger Van" 2013 6001
"2008, SATURN, VUE, XR"                                1 "SUV"           2013 6001
"1999, LEXUS, RX, 300"                                12 "SUV"           2013 6001
"2012, VOLVO, S80, T6"                                 1 "Sedan"         2013 6001
"2000, TOYOTA, TACOMA, XTRACAB"                        0 ""              2013 6001
"1999, FORD, WINDSTAR, "                               0 ""              2013 6001
"2006, FORD, F250, SUPER DUTY"                         0 ""              2013 6001
"2011, HYUNDAI, GENESIS COUPE, 2.0T"                   1 "Coupe"         2013 6001
"2001, LEXUS, ES, 300"                                 7 "Sedan"         2013 6001
"2011, HONDA, CR-V, SE"                                7 "SUV"           2013 6001
"1977, FORD, F250, "                                   0 ""              2013 6001
"2006, VOLKSWAGEN, NEW BEETLE, 2.5L OPTION PACKAGE 1"  1 "Hatchback"     2013 6001
"1996, FORD, PROBE, BASE/SE"                           1 "Hatchback"     2013 6001
"2002, HONDA, ODYSSEY, EXL"                            9 "Passenger Van" 2013 6001
"2000, BMW, 528, I AUTOMATIC"                          3 "Sedan"         2013 6001
"1999, FORD, EXPLORER, "                               3 "SUV"           2013 6001
"2005, HONDA, PILOT, EX"                               6 "SUV"           2013 6001
"2010, HONDA, CR-V, EXL"                              27 "SUV"           2013 6001
"2004, DODGE, GRAND CARAVAN, SXT"                      2 "Passenger Van" 2013 6001
"2003, HONDA, CIVIC, LX"                               9 "Sedan"         2013 6001
"1999, DODGE, RAM 1500, "                              0 ""              2013 6001
"2000, SUBARU, FORESTER, S"                            2 "SUV"           2013 6001
"1991, BUICK, REGAL, LIMITED"                          1 "Sedan"         2013 6001
"2003, BMW, 325, I"                                    5 "Sedan"         2013 6001
"2001, FORD, FOCUS, SE/SE COMFORT"                     1 "Sedan"         2013 6001
"2001, FORD, WINDSTAR, SE SPORT"                       1 "Passenger Van" 2013 6001
"1991, TOYOTA, CAMRY, "                                2 "Sedan"         2013 6001
"2006, BMW, X5, 4.4I"                                  1 "SUV"           2013 6001
"1996, HONDA, ACCORD, EX/EX-R"                         1 "Sedan"         2013 6001
"2008, CADILLAC, SRX, "                                1 "SUV"           2013 6001
"2006, JEEP, WRANGLER, X"                              3 "SUV"           2013 6001
"2011, LEXUS, ES, 350"                                 6 "Sedan"         2013 6001
"1985, MERCEDES-BENZ, 380, SL"                         1 "Convertible"   2013 6001
"1994, JAGUAR, XJS, 2+2"                               2 "Convertible"   2013 6001
"1990, FORD, F350, "                                   0 ""              2013 6001
"2002, FORD, THUNDERBIRD, "                            4 "Convertible"   2013 6001
"2004, SATURN, L300, LEVEL 1"                          1 "Sedan"         2013 6001
"1999, JEEP, CHEROKEE, SPORT/CLASSIC"                 13 "SUV"           2013 6001
"2011, SUBARU, LEGACY, 2.5I PREMIUM"                   9 "Sedan"         2013 6001
"2003, HONDA, CR-V, LX"                                2 "SUV"           2013 6001
"2007, SAAB, 9-3, 2.0T"                                3 "Convertible"   2013 6001
"1991, FORD, ESCORT, LX"                               1 "Hatchback"     2013 6001
"2009, CHEVROLET, IMPALA, 1LT"                         1 "Sedan"         2013 6001
"2001, FORD, RANGER, SUPER CAB"                        0 ""              2013 6001
"2008, PORSCHE, 911, TURBO"                            1 "Convertible"   2013 6001
"2011, ACURA, TL, "                                    1 "Sedan"         2013 6001
"2010, MAZDA, CX-9, "                                 12 "SUV"           2013 6001
"2003, FORD, FOCUS, SE/SE SPORT/ZTW"                   3 "Wagon"         2013 6001
"1996, FORD, BRONCO, U100"                             1 "SUV"           2013 6001
"2010, FORD, F250, SUPER DUTY"                         2 "Pickup Truck"  2013 6001
"2000, CHEVROLET, CORVETTE, "                          1 "Coupe"         2013 6001
"2007, HONDA, CR-V, EX"                               10 "SUV"           2013 6001
"2013, SUBARU, FORESTER, 2.5X PREMIUM"                 2 "SUV"           2013 6001
"1999, MERCEDES-BENZ, SL, 500"                         2 "Convertible"   2013 6001
"2007, MERCEDES-BENZ, SL, 600"                         1 "Convertible"   2013 6001
"2006, FORD, TAURUS, SE"                               4 "Sedan"         2013 6001
"2008, LEXUS, LS, 460"                                 1 "Sedan"         2013 6001
"1994, FORD, ESCORT, LX/SPORT"                         2 "Hatchback"     2013 6001
"2002, HONDA, ACCORD, EX/SE"                          18 "Sedan"         2013 6001
"2011, GMC, YUKON XL, K1500 SLT"                       2 "SUV"           2013 6001
"2011, CHEVROLET, TRAVERSE, LTZ"                       2 "SUV"           2013 6001
"1998, JEEP, WRANGLER / TJ, SPORT"                     1 "SUV"           2013 6001
"1995, BMW, 530, I"                                    1 "Sedan"         2013 6001
"2004, PORSCHE, 911, CARRERA/CARRERA 4/CARRERA 4S"     1 "Convertible"   2013 6001
"2009, CHRYSLER, TOWN & COUNTRY, TOURING"              1 "Passenger Van" 2013 6001
"2010, CHEVROLET, AVEO, LS/LT"                         1 "Hatchback"     2013 6001
"2000, GMC, JIMMY / ENVOY, "                           3 "SUV"           2013 6001
"2009, BMW, 335, XI"                                   1 "Coupe"         2013 6001
"2012, TOYOTA, SIENNA, XLE/LIMITED"                    7 "Passenger Van" 2013 6001
"1966, FORD, MUSTANG, "                                3 "Coupe"         2013 6001
"1967, VOLKSWAGEN, TYPE 1, "                           1 "Sedan"         2013 6001
"2003, CHEVROLET, MONTE CARLO, LS"                     1 "Coupe"         2013 6001
"1986, MERCURY, MARQUIS, COLONY PARK"                  1 "Wagon"         2013 6001
"1992, LINCOLN, TOWN CAR, SIGNATURE"                   1 "Sedan"         2013 6001
"2009, FORD, FLEX, LIMITED"                            1 "SUV"           2013 6001
"2013, BMW, 335, XI"                                   1 "Sedan"         2013 6001
"2004, INFINITI, I35, "                                2 "Sedan"         2013 6001
"2000, VOLVO, S40, "                                   3 "Sedan"         2013 6001
"2009, VOLKSWAGEN, CC, SPORT"                          1 "Sedan"         2013 6001
"2009, CHEVROLET, TRAVERSE, LT"                        1 "SUV"           2013 6001
"2007, TOYOTA, RAV4, LIMITED"                          7 "SUV"           2013 6001
"2005, MERCEDES-BENZ, SLK, 350"                        2 "Convertible"   2013 6001
"2008, FORD, ESCAPE, XLT"                              6 "SUV"           2013 6001
"2005, VOLKSWAGEN, NEW JETTA, VALUE"                   1 "Sedan"         2013 6001
"2002, BMW, 325, CI"                                   1 "Convertible"   2013 6001
end

Thank you in advance for any help.

Adam

[Hemanshu Kumar]

I am not entirely clear on what you are trying to do, but does using frequency weights solve the problem?

groups Make_Model_Year [fw=vehicles] if datayear == 2013 & body_style == "Sedan", order(h) select(10)

[Adam Gallaher]

Thank you, Hemanshu this did provide the answer I was looking for. Basically, if for example I take the vehicle which tops the frequency list lets say it is a 2004 Honda Accord and sum vehicles if Make_Model_Year = that vehicle & datayear == 2013 and then display r(sum) after, that is the answer I am looking for.