Announcement

Collapse
No announcement yet.
X
Collapse
  • Page of 1
  • Filter
  • Time
  • Show
Clear All
new posts
  • #1

    Generating a variable to count repetitions of observations based on group and time -rangestat-

    Dear Statalists,

    I am using Stata/MP 16.1 to analyze a large dataset (200,000 obs.) of investor lead manager relationships. I want to how many times any investor has worked with the lead underwriter in the hot IPO (the variable HOTIPO = 1) within 1826 days. I used -rangestat-. Here's the code I used when not considering the variable HOTIPO:
    gen long obs_no = _n
    rangestat (count) wanted = obs_no, by( Investor LeadManager ) interval( IssueDate -1826 -1)

    Also, is there a way that I can assign equal weights to the LeadManger? For example, in the third line, "1798 GLOBAL PTNR (CAYMAN) LTD" "CREDIT-SUISSE" , the number of LeadManager is 3, so I want the count to be 1/3 instead of 1.

    Thank you so much your time and help!

    The data example is:
    ----------------------- copy starting from the next line -----------------------
    Code:
    * Example generated by -dataex-. For more info, type help dataex
clear
input int IssueDate str31(StockName Investor) str30 LeadManager byte(HOTIPO NumberofLeadManagers) long obs_no double wanted
18828 "SUNCOKE ENERGY INC."             "1798 GLOBAL PTNR (CAYMAN) LTD"  "BOA-MERRILL"         0 3   1 .
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD"  "CREDIT-SUISSE"       1 4   2 .
18828 "SUNCOKE ENERGY INC."             "1798 GLOBAL PTNR (CAYMAN) LTD"  "CREDIT-SUISSE"       0 3   3 1
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD"  "GS"                  1 4   4 .
18828 "SUNCOKE ENERGY INC."             "1798 GLOBAL PTNR (CAYMAN) LTD"  "GS"                  0 3   5 1
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD"  "JP Morgan (JPM)"     1 4   6 .
18786 "FUSION-IO INC."                  "1798 GLOBAL PTNR (CAYMAN) LTD"  "Morgan Stanley & Co" 1 4   7 .
20669 "AT HOME GROUP INC."              "1ST GLOBAL ADVISORS, INC"       "BOA-MERRILL"         0 4   8 .
20740 "MAMMOTH ENERGY SERVICES INC."    "1ST GLOBAL ADVISORS, INC"       "Barclays Capital"    0 3   9 .
20746 "FORTERRA INC."                   "1ST GLOBAL ADVISORS, INC"       "Barclays Capital"    0 6  10 1
20746 "FORTERRA INC."                   "1ST GLOBAL ADVISORS, INC"       "CITIGROUP"           0 6  11 .
20740 "MAMMOTH ENERGY SERVICES INC."    "1ST GLOBAL ADVISORS, INC"       "CREDIT-SUISSE"       0 3  12 .
20746 "FORTERRA INC."                   "1ST GLOBAL ADVISORS, INC"       "CREDIT-SUISSE"       0 6  13 1
20746 "FORTERRA INC."                   "1ST GLOBAL ADVISORS, INC"       "DEUTSCHE-BANK"       0 6  14 .
20746 "FORTERRA INC."                   "1ST GLOBAL ADVISORS, INC"       "GS"                  0 6  15 1
20669 "AT HOME GROUP INC."              "1ST GLOBAL ADVISORS, INC"       "GS"                  0 4  16 .
20669 "AT HOME GROUP INC."              "1ST GLOBAL ADVISORS, INC"       "JEFFERIES"           0 4  17 .
20669 "AT HOME GROUP INC."              "1ST GLOBAL ADVISORS, INC"       "Morgan Stanley & Co" 0 4  18 .
20740 "MAMMOTH ENERGY SERVICES INC."    "1ST GLOBAL ADVISORS, INC"       "Piper Jaffray Inc"   0 3  19 .
20746 "FORTERRA INC."                   "1ST GLOBAL ADVISORS, INC"       "RBC Capital Markets" 0 6  20 .
18946 "CLOVIS ONCOLOGY INC."            "ABINGWORTH LLP"                 "CREDIT-SUISSE"       0 2  21 .
20717 "NOVAN INC."                      "ABINGWORTH LLP"                 "JMP-SEC"             1 3  22 .
18946 "CLOVIS ONCOLOGY INC."            "ABINGWORTH LLP"                 "JP Morgan (JPM)"     0 2  23 .
20717 "NOVAN INC."                      "ABINGWORTH LLP"                 "Piper Jaffray Inc"   1 3  24 .
20717 "NOVAN INC."                      "ABINGWORTH LLP"                 "WEDBUSH-SEC"         1 3  25 .
20649 "ADVANCEPIERRE FOODS HLDG INC."   "ACADIAN ASSET MANAGEMENT LLC"   "BMO-CAPITAL"         1 6  26 .
20649 "ADVANCEPIERRE FOODS HLDG INC."   "ACADIAN ASSET MANAGEMENT LLC"   "Barclays Capital"    1 6  27 .
20649 "ADVANCEPIERRE FOODS HLDG INC."   "ACADIAN ASSET MANAGEMENT LLC"   "CREDIT-SUISSE"       1 6  28 .
20649 "ADVANCEPIERRE FOODS HLDG INC."   "ACADIAN ASSET MANAGEMENT LLC"   "DEUTSCHE-BANK"       1 6  29 .
20649 "ADVANCEPIERRE FOODS HLDG INC."   "ACADIAN ASSET MANAGEMENT LLC"   "GS"                  1 6  30 .
20649 "ADVANCEPIERRE FOODS HLDG INC."   "ACADIAN ASSET MANAGEMENT LLC"   "Morgan Stanley & Co" 1 6  31 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "BOA-MERRILL"         0 9  32 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "Barclays Capital"    0 9  33 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "CITIGROUP"           0 9  34 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "CREDIT-SUISSE"       0 9  35 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "DEUTSCHE-BANK"       0 9  36 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "GS"                  0 9  37 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "JP Morgan (JPM)"     0 9  38 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "Morgan Stanley & Co" 0 9  39 .
18695 "HCA HOLDINGS INC."               "ACCIPITER CAPITAL MGMT, L.L.C." "WELLS-FARGO"         0 9  40 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "CITIGROUP"           0 7  41 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "CREDIT-SUISSE"       0 7  42 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "DEUTSCHE-BANK"       0 7  43 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "JP Morgan (JPM)"     0 7  44 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "RBC Capital Markets" 0 7  45 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "UBS Investment Bank" 0 7  46 .
20614 "ATKORE INTERNATIONAL GROUP INC." "ACK ASSET MANAGEMENT LLC"       "WELLS-FARGO"         0 7  47 .
18750 "BOINGO WIRELESS INC."            "ACT II CAPITAL, LLC"            "CREDIT-SUISSE"       0 2  48 .
18834 "TANGOE INC."                     "ACT II CAPITAL, LLC"            "DEUTSCHE-BANK"       1 2  49 1
18750 "BOINGO WIRELESS INC."            "ACT II CAPITAL, LLC"            "DEUTSCHE-BANK"       0 2  50 .
18834 "TANGOE INC."                     "ACT II CAPITAL, LLC"            "STIFEL"              1 2  51 .
20752 "RA PHARMACEUTICALS INC."         "ACUTA CAPITAL PARTNERS, LLC"    "BMO-CAPITAL"         0 4  52 .
20752 "RA PHARMACEUTICALS INC."         "ACUTA CAPITAL PARTNERS, LLC"    "CREDIT-SUISSE"       0 4  53 .
20752 "RA PHARMACEUTICALS INC."         "ACUTA CAPITAL PARTNERS, LLC"    "JEFFERIES"           0 4  54 .
20752 "RA PHARMACEUTICALS INC."         "ACUTA CAPITAL PARTNERS, LLC"    "SUNTRUST-RH"         0 4  55 .
20752 "RA PHARMACEUTICALS INC."         "ADAGE CAPITAL MANAGEMENT, L.P." "BMO-CAPITAL"         0 4  56 2
20676 "PROTAGONIST THERAPEUTICS INC."   "ADAGE CAPITAL MANAGEMENT, L.P." "BMO-CAPITAL"         0 3  57 1
20494 "AVEXIS INC."                     "ADAGE CAPITAL MANAGEMENT, L.P." "BMO-CAPITAL"         0 3  58 .
18827 "SKULLCANDY INC."                 "ADAGE CAPITAL MANAGEMENT, L.P." "BOA-MERRILL"         0 2  59 1
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "BOA-MERRILL"         0 9  60 .
20654 "AUDENTES THERAPEUTICS INC."      "ADAGE CAPITAL MANAGEMENT, L.P." "BOA-MERRILL"         0 3  61 .
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "Barclays Capital"    1 8  62 1
18668 "KINDER MORGAN INC."              "ADAGE CAPITAL MANAGEMENT, L.P." "Barclays Capital"    0 2  63 .
20676 "PROTAGONIST THERAPEUTICS INC."   "ADAGE CAPITAL MANAGEMENT, L.P." "Barclays Capital"    0 3  64 .
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "Barclays Capital"    0 9  65 1
20719 "VALVOLINE INC."                  "ADAGE CAPITAL MANAGEMENT, L.P." "CITIGROUP"           0 6  66 .
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "CITIGROUP"           1 8  67 1
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "CITIGROUP"           0 9  68 .
20654 "AUDENTES THERAPEUTICS INC."      "ADAGE CAPITAL MANAGEMENT, L.P." "COWEN"               0 3  69 .
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "CREDIT-SUISSE"       1 8  70 1
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "CREDIT-SUISSE"       0 9  71 .
20752 "RA PHARMACEUTICALS INC."         "ADAGE CAPITAL MANAGEMENT, L.P." "CREDIT-SUISSE"       0 4  72 2
20676 "MEDPACE INC."                    "ADAGE CAPITAL MANAGEMENT, L.P." "CREDIT-SUISSE"       1 4  73 .
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "DEUTSCHE-BANK"       0 9  74 .
20719 "VALVOLINE INC."                  "ADAGE CAPITAL MANAGEMENT, L.P." "DEUTSCHE-BANK"       0 6  75 .
20494 "AVEXIS INC."                     "ADAGE CAPITAL MANAGEMENT, L.P." "GS"                  0 3  76 2
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "GS"                  1 8  77 2
18668 "KINDER MORGAN INC."              "ADAGE CAPITAL MANAGEMENT, L.P." "GS"                  0 2  78 .
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "GS"                  0 9  79 1
20719 "VALVOLINE INC."                  "ADAGE CAPITAL MANAGEMENT, L.P." "GS"                  0 6  80 1
20676 "MEDPACE INC."                    "ADAGE CAPITAL MANAGEMENT, L.P." "JEFFERIES"           1 4  81 2
20671 "GEMPHIRE THERAPEUTICS INC."      "ADAGE CAPITAL MANAGEMENT, L.P." "JEFFERIES"           0 2  82 1
20752 "RA PHARMACEUTICALS INC."         "ADAGE CAPITAL MANAGEMENT, L.P." "JEFFERIES"           0 4  83 3
20494 "AVEXIS INC."                     "ADAGE CAPITAL MANAGEMENT, L.P." "JEFFERIES"           0 3  84 .
20719 "ELF BEAUTY INC."                 "ADAGE CAPITAL MANAGEMENT, L.P." "JP Morgan (JPM)"     1 4  85 .
20719 "VALVOLINE INC."                  "ADAGE CAPITAL MANAGEMENT, L.P." "JP Morgan (JPM)"     0 6  86 .
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "JP Morgan (JPM)"     0 9  87 .
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "KEYBANC"             1 8  88 .
20676 "PROTAGONIST THERAPEUTICS INC."   "ADAGE CAPITAL MANAGEMENT, L.P." "LEERINK-PARTNER"     0 3  89 .
20719 "VALVOLINE INC."                  "ADAGE CAPITAL MANAGEMENT, L.P." "ML"                  0 6  90 .
18827 "SKULLCANDY INC."                 "ADAGE CAPITAL MANAGEMENT, L.P." "Morgan Stanley & Co" 0 2  91 1
18695 "HCA HOLDINGS INC."               "ADAGE CAPITAL MANAGEMENT, L.P." "Morgan Stanley & Co" 0 9  92 .
20719 "VALVOLINE INC."                  "ADAGE CAPITAL MANAGEMENT, L.P." "Morgan Stanley & Co" 0 6  93 .
20719 "ELF BEAUTY INC."                 "ADAGE CAPITAL MANAGEMENT, L.P." "Morgan Stanley & Co" 1 4  94 .
20654 "AUDENTES THERAPEUTICS INC."      "ADAGE CAPITAL MANAGEMENT, L.P." "PIPER-MUNI"          0 3  95 .
20719 "ELF BEAUTY INC."                 "ADAGE CAPITAL MANAGEMENT, L.P." "Piper Jaffray Inc"   1 4  96 .
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "RBC Capital Markets" 1 8  97 1
20671 "GEMPHIRE THERAPEUTICS INC."      "ADAGE CAPITAL MANAGEMENT, L.P." "RBC Capital Markets" 0 2  98 .
20752 "RA PHARMACEUTICALS INC."         "ADAGE CAPITAL MANAGEMENT, L.P." "SUNTRUST-RH"         0 4  99 1
20738 "EXTRACTION OIL & GAS INC."       "ADAGE CAPITAL MANAGEMENT, L.P." "SUNTRUST-RH"         1 8 100 .
end
format %tdnn/dd/CCYY IssueDate
    ------------------ copy up to and including the previous line ------------------
    Last edited by Claire wen; 28 Apr 2021, 05:42.
    Tags: None
  • #2
    Code:
    gen count_weight = cond(HOTIPO, 1/NumberofLeadManagers, 0)
rangestat (sum) wanted = count_weight, by( Investor LeadManager ) interval( IssueDate -1826 -1)
    • 1 like

    Comment

    • #3
      Clyde, thank you so much! It works!

      I have another question. What can I do if I want to count how many times any investor has worked with the lead underwriter in the hot IPO (the variable HOTIPO = 1) within 1826 days without considering the weights? I tried code:

      gen long obs_no=_n cond(HOTIPO, 0)
      rangestat (count) wanted = obs_no1, by( Investor LeadManager ) interval( IssueDate -1826 -1)

      But it doesn't work. Could you please help again?

      Comment

      • #4
        This one is actually simpler:

        Code:
        rangestat (sum) wanted = HOTIPO, by( Investor LeadManager ) interval( IssueDate -1826 -1)
        The key things to remember are: indicators for events (like HOTIPO) are best represented with a 0/1 variable,as you have done. With that coding, the number of times the event happens (HOTIPO == 1) is given by the (sum) operator. (count) is a different operator, that gives you the number of non-missing values. But with HOTIPO, there are no missing values, so (count)ing it just gives you the total number of joint activities between the Investor and LeadManger, irrespective of whether it is an IPO or not.
        Last edited by Clyde Schechter; 30 Apr 2021, 16:47.
        • 1 like

        Comment

        • #5
          Thank you, Clyde! I really appreciate your help!

          Comment

          • #6
            Dear Statalists,

            I have another count issue regarding this dataset. I want to count the number of stocks (without repetitions) an investor has invested in within 1826 days. I tried bysort Investor StockName, gen n=_N, but the results were not what I wanted.

            Thank you so much for your time and help!

            Comment

            • #7
              The code you tried will give you, for each stock, the number of times the investor bought it. What you want is more complicated. Using the example data from #1:

              Code:
              preserve
keep IssueDate StockName Investor
tempfile holding
save `holding'
restore

rangejoin IssueDate -1826 -1 using `holding', by(Investor)

by obs_no StockName_U, sort: gen stock_count = (_n == 1)
by obs_no (StockName_U): replace stock_count = sum(stock_count)
by obs_no (StockName_U): keep if _n == _N
drop *_U
              The variable stock_count contains the result you are seeking.

              Comment

              • #8
                See also #4 at https://www.statalist.org/forums/for...evious-5-years for references to use of rangerun and distinct.
                • 1 like

                Comment

                • #9
                  Originally posted by Clyde Schechter View Post
                  The code you tried will give you, for each stock, the number of times the investor bought it. What you want is more complicated. Using the example data from #1:

                  Code:
                  preserve
keep IssueDate StockName Investor
tempfile holding
save `holding'
restore

rangejoin IssueDate -1826 -1 using `holding', by(Investor)

by obs_no StockName_U, sort: gen stock_count = (_n == 1)
by obs_no (StockName_U): replace stock_count = sum(stock_count)
by obs_no (StockName_U): keep if _n == _N
drop *_U
                  The variable stock_count contains the result you are seeking.
                  Hi Clyde, thanks so much for your help! I tried the code you wrote, but there's a bit different than what I want. For example, the result for "SUNCOKE ENERGY INC." "1798 GLOBAL PTNR (CAYMAN) LTD" is 1 as the variable stock_count shows, but the result for "FUSION-IO INC." "1798 GLOBAL PTNR (CAYMAN) LTD" shoud be 0 instead of 1 in the stock_count, because the investor "1798 GLOBAL PTNR (CAYMAN) LTD" didn't invest in any stock prior to "FUSION-IO INC.". Could you please help me modify the code?

                  Comment

                  • #10
                    Ah, yes, I see the problem.

                    Code:
                    preserve
keep IssueDate StockName Investor
tempfile holding
save `holding'
restore

rangejoin IssueDate -1826 -1 using `holding', by(Investor)

by obs_no StockName_U, sort: gen stock_count = (_n == 1 & !missing(StockName_U))
by obs_no (StockName_U): replace stock_count = sum(stock_count)
by obs_no (StockName_U): keep if _n == _N
drop *_U

                    Comment

                    • #11
                      Thanks a lot Clyde! It works!

                      Comment

                      Previous Next
                      Working...
                      X