counting based on groups

[claire deng]

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!

* 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

[claire deng]

I've tried using

gen count_weight = cond(hot, 1/NumberofLeadManagers, 0)
rangestat (sum) wanted = count_weight1, by( Investor manager) interval( IssueDate 1 365)

to calculate the number of hot firms an investor has with the manager. Then similar code by(investor) to calculate the number of hot firms an investor has with all the managers, and the subtract the number. But this is a little bit different from what I want. When counting the number of hot firms an investor has with other manager, I want to exclude the observations that it has with the manager.