Dear Statalists,

I'm having problem counting based on groups. I have quite a large dataset, around 100,000 observations. I want to count the number of hot firms (hot=1) an investor has with other managers (excluding the one of the focal issuedate) within a year of the current firm (so the date is (1, 365)). The count weight is the weighted of NumberofLeadManagers, and I want to number based on weights.

I have a simple data example attached.

Thanks so much for your help!


Code:
* Example generated by -dataex-. For more info, type help dataex
clear
input int IssueDate str36 firm str31 Investor str35 manager byte(hot NumberofLeadManagers) float count_weight
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "Barclays Capital"               1  7 .14285715
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "Credit Suisse"                  1  7 .14285715
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "Goldman Sachs & Co"             1  7 .14285715
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "JP Morgan (JPM)"                1  7 .14285715
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "Jefferies & Co Inc"             1  7 .14285715
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "Morgan Stanley & Co"            1  7 .14285715
21810 "DATADOG INC."                    "1 NORTH WEALTH SERVICES, LLC"  "RBC Capital Markets"            1  7 .14285715
19928 "ADVANCED DRAINAGE SYSTEMS INC."  "12 WEST CAPITAL MGMT, L.P."    "Barclays Capital"               1  4         0
20117 "SHAKE SHACK INC."                "12 WEST CAPITAL MGMT, L.P."    "Barclays Capital"               1  5         0
19928 "ADVANCED DRAINAGE SYSTEMS INC."  "12 WEST CAPITAL MGMT, L.P."    "CITIGROUP"                      1  4         0
19928 "ADVANCED DRAINAGE SYSTEMS INC."  "12 WEST CAPITAL MGMT, L.P."    "Deutsche Bank Securities Corp"  1  4         0
20117 "SHAKE SHACK INC."                "12 WEST CAPITAL MGMT, L.P."    "Goldman Sachs & Co"             1  5         0
20117 "SHAKE SHACK INC."                "12 WEST CAPITAL MGMT, L.P."    "JP Morgan (JPM)"                1  5         0
21300 "SMARTSHEET INC."                 "12 WEST CAPITAL MGMT, L.P."    "JP Morgan (JPM)"                1  4       .25
20117 "SHAKE SHACK INC."                "12 WEST CAPITAL MGMT, L.P."    "Jefferies & Co Inc"             1  5         0
21300 "SMARTSHEET INC."                 "12 WEST CAPITAL MGMT, L.P."    "Jefferies & Co Inc"             1  4       .25
20117 "SHAKE SHACK INC."                "12 WEST CAPITAL MGMT, L.P."    "Morgan Stanley & Co"            1  5         0
21300 "SMARTSHEET INC."                 "12 WEST CAPITAL MGMT, L.P."    "Morgan Stanley & Co"            1  4       .25
21362 "NEURONETICS INC."                "12 WEST CAPITAL MGMT, L.P."    "Piper Jaffray Inc"              1  2         0
19928 "ADVANCED DRAINAGE SYSTEMS INC."  "12 WEST CAPITAL MGMT, L.P."    "RBC Capital Markets"            1  4         0
21300 "SMARTSHEET INC."                 "12 WEST CAPITAL MGMT, L.P."    "RBC Capital Markets"            1  4       .25
21362 "NEURONETICS INC."                "12 WEST CAPITAL MGMT, L.P."    "William Blair & Co"             1  2         0
19739 "RSP PERMIAN INC."                "1492 CAPITAL MANAGEMENT, LLC"  "Barclays Capital"               1  6         0
20037 "THE JOINT CORP."                 "1492 CAPITAL MANAGEMENT, LLC"  "Feltl & Co"                     1  2         0
19739 "RSP PERMIAN INC."                "1492 CAPITAL MANAGEMENT, LLC"  "JP Morgan (JPM)"                1  6         0
19948 "C1 FINANCIAL INC"                "1492 CAPITAL MANAGEMENT, LLC"  "Keefe Bruyette & Woods Inc"     0  2         0
19739 "RSP PERMIAN INC."                "1492 CAPITAL MANAGEMENT, LLC"  "RBC Capital Markets"            1  6         0
19948 "C1 FINANCIAL INC"                "1492 CAPITAL MANAGEMENT, LLC"  "Raymond James & Associates Inc" 0  2         0
19739 "RSP PERMIAN INC."                "1492 CAPITAL MANAGEMENT, LLC"  "Raymond James & Associates Inc" 1  6         0
21216 "ONE STOP SYSTEMS INC."           "1492 CAPITAL MANAGEMENT, LLC"  "Roth Capital Partners Inc"      0  1         0
20037 "THE JOINT CORP."                 "1492 CAPITAL MANAGEMENT, LLC"  "Roth Capital Partners Inc"      1  2         0
21361 "THE LOVESAC CO."                 "1492 CAPITAL MANAGEMENT, LLC"  "Roth Capital Partners Inc"      1  1         1
19739 "RSP PERMIAN INC."                "1492 CAPITAL MANAGEMENT, LLC"  "TUDOR-PICKERING"                1  6         0
19739 "RSP PERMIAN INC."                "1492 CAPITAL MANAGEMENT, LLC"  "UBS Investment Bank"            1  6         0
19094 "FORUM ENERGY TECHNOLOGIES INC."  "1798 GLOBAL PTNR (CAYMAN) LTD" "BOA-MERRILL"                    1  5         0
18828 "SUNCOKE ENERGY INC."             "1798 GLOBAL PTNR (CAYMAN) LTD" "BOA-MERRILL"                    1  3         0
19276 "REALOGY HOLDINGS CORP."          "1798 GLOBAL PTNR (CAYMAN) LTD" "Barclays Capital"               1  4       .25
19094 "FORUM ENERGY TECHNOLOGIES INC."  "1798 GLOBAL PTNR (CAYMAN) LTD" "CITIGROUP"                      1  5         0
19094 "FORUM ENERGY TECHNOLOGIES INC."  "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse"                  1  5         0
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse"                  1  4       .25
19276 "REALOGY HOLDINGS CORP."          "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse"                  1  4       .25
18828 "SUNCOKE ENERGY INC."             "1798 GLOBAL PTNR (CAYMAN) LTD" "Credit Suisse"                  1  3         0
19094 "FORUM ENERGY TECHNOLOGIES INC."  "1798 GLOBAL PTNR (CAYMAN) LTD" "Deutsche Bank Securities Corp"  1  5         0
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD" "Goldman Sachs & Co"             1  4       .25
19276 "REALOGY HOLDINGS CORP."          "1798 GLOBAL PTNR (CAYMAN) LTD" "Goldman Sachs & Co"             1  4       .25
18828 "SUNCOKE ENERGY INC."             "1798 GLOBAL PTNR (CAYMAN) LTD" "Goldman Sachs & Co"             1  3         0
19094 "FORUM ENERGY TECHNOLOGIES INC."  "1798 GLOBAL PTNR (CAYMAN) LTD" "JP Morgan (JPM)"                1  5         0
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD" "JP Morgan (JPM)"                1  4       .25
19276 "REALOGY HOLDINGS CORP."          "1798 GLOBAL PTNR (CAYMAN) LTD" "JP Morgan (JPM)"                1  4       .25
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD" "Morgan Stanley & Co"            1  4       .25
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "BMO Capital Markets"            1  8      .125
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "BMO Capital Markets"            1 13         0
19844 "ARES MANAGEMENT LP"              "1832 ASSET MANAGEMENT L.P."    "BOA-MERRILL"                    0  5         0
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "BOA-MERRILL"                    1  8      .125
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "BOA-MERRILL"                    1 13         0
20935 "FLOOR & DECOR HOLDINGS INC."     "1832 ASSET MANAGEMENT L.P."    "Barclays Capital"               1  8      .125
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "Barclays Capital"               1  8      .125
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "Barclays Capital"               1  7 .14285715
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "Barclays Capital"               1 13         0
20838 "KEANE GROUP INC."                "1832 ASSET MANAGEMENT L.P."    "CITIGROUP"                      1  4       .25
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "CITIGROUP"                      1  8      .125
19753 "NEW HOME CO INC."                "1832 ASSET MANAGEMENT L.P."    "CITIGROUP"                      1  3  .3333333
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "CITIGROUP"                      1 13         0
20935 "FLOOR & DECOR HOLDINGS INC."     "1832 ASSET MANAGEMENT L.P."    "Credit Suisse"                  1  8      .125
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "Credit Suisse"                  0  8         0
19753 "NEW HOME CO INC."                "1832 ASSET MANAGEMENT L.P."    "Credit Suisse"                  1  3  .3333333
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "Credit Suisse"                  1  7 .14285715
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "Credit Suisse"                  1 13         0
19829 "SPORTSMAN'S WAREHOUSE HLDG INC." "1832 ASSET MANAGEMENT L.P."    "Credit Suisse"                  1  2         0
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "Deutsche Bank Securities Corp"  0  8         0
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "Deutsche Bank Securities Corp"  1  7 .14285715
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "Deutsche Bank Securities Corp"  1 13         0
19844 "ARES MANAGEMENT LP"              "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             0  5         0
20935 "FLOOR & DECOR HOLDINGS INC."     "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             1  8      .125
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             0  8         0
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             1  8      .125
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             1  7 .14285715
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             1 13         0
19829 "SPORTSMAN'S WAREHOUSE HLDG INC." "1832 ASSET MANAGEMENT L.P."    "Goldman Sachs & Co"             1  2         0
19844 "ARES MANAGEMENT LP"              "1832 ASSET MANAGEMENT L.P."    "JP Morgan (JPM)"                0  5         0
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "JP Morgan (JPM)"                0  8         0
20838 "KEANE GROUP INC."                "1832 ASSET MANAGEMENT L.P."    "JP Morgan (JPM)"                1  4       .25
19753 "NEW HOME CO INC."                "1832 ASSET MANAGEMENT L.P."    "JP Morgan (JPM)"                1  3  .3333333
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "JP Morgan (JPM)"                1  7 .14285715
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "JP Morgan (JPM)"                1 13         0
20935 "FLOOR & DECOR HOLDINGS INC."     "1832 ASSET MANAGEMENT L.P."    "Jefferies & Co Inc"             1  8      .125
20935 "FLOOR & DECOR HOLDINGS INC."     "1832 ASSET MANAGEMENT L.P."    "Merrill Lynch & Co Inc"         1  8      .125
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "Merrill Lynch & Co Inc"         0  8         0
20838 "KEANE GROUP INC."                "1832 ASSET MANAGEMENT L.P."    "Merrill Lynch & Co Inc"         1  4       .25
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "Merrill Lynch & Co Inc"         1  7 .14285715
19844 "ARES MANAGEMENT LP"              "1832 ASSET MANAGEMENT L.P."    "Morgan Stanley & Co"            0  5         0
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "Morgan Stanley & Co"            0  8         0
20838 "KEANE GROUP INC."                "1832 ASSET MANAGEMENT L.P."    "Morgan Stanley & Co"            1  4       .25
20193 "PARTY CITY HOLDCO INC."          "1832 ASSET MANAGEMENT L.P."    "Morgan Stanley & Co"            1  7 .14285715
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "Morgan Stanley & Co"            1 13         0
20935 "FLOOR & DECOR HOLDINGS INC."     "1832 ASSET MANAGEMENT L.P."    "Piper Jaffray Inc"              1  8      .125
20850 "INVITATION HOMES INC."           "1832 ASSET MANAGEMENT L.P."    "RBC Capital Markets"            0  8         0
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "RBC Capital Markets"            1  8      .125
19745 "SANTANDER CONSUMER USA HLDGS"    "1832 ASSET MANAGEMENT L.P."    "RBC Capital Markets"            1 13         0
19886 "MEMORIAL RESOURCE DVLP CORP."    "1832 ASSET MANAGEMENT L.P."    "Raymond James & Associates Inc" 1  8      .125
end
format %tdnn/dd/CCYY IssueDate