Screen for most recent data

[Edward Wu]

Hey!

I have a panel data. cusip is the company ID. fpedates is the quarter end date. anndates is the announce date for analysts. analys is the id of analysts

For every fpedates of each cusip, I want to keep only one analys (the one with the most recent anndates).

Also, anndates needs to be ahead of fpedates but not more than 50 days before.

In addition, I need there are at least two analys for each cusip in every quarter (drop the cusip quarters with less than two analys).

Anyone can help? Great thanks!

Here are the data example:

* Example generated by -dataex-. To install: ssc install dataex
clear
input str8 cusip double analys long(fpedats anndats)
"25468710"  54440 21639 21586
"25468710"   9584 21639 21586
"25468710"  71755 21639 21586
"25468710"  46442 21639 21585
"25468710"  54310 21639 21586
"25468710" 595335 21639 21584
"25468710" 582439 21639 21586
"25468710" 500876 21639 21586
"25468710" 554250 21639 21586
"25468710"   1717 21639 21586
"25468710" 593008 21639 21585
"25468710" 581246 21639 21586
"25468710"  82890 21639 21586
"25468710" 121968 21639 21587
"25468710" 564583 21639 21586
"25468710" 507503 21639 21587
"25468710" 595335 21639 21587
"25468710" 111523 21639 21586
"25468710"  19083 21639 21590
"25468710"  71755 21639 21593
"25468710" 106603 21639 21599
"25468710"  71755 21639 21614
"25468710" 554250 21639 21628
"25468710"  46442 21639 21630
"25468710" 582439 21639 21635
"25468710" 111523 21639 21640
"25468710" 137702 21639 21641
"25468710" 500876 21639 21643
"25468710"  46442 21639 21643
"25468710" 535432 21639 21647
"25468710" 621768 21639 21647
"25468710" 564583 21639 21649
"25468710"   9584 21639 21651
"25468710"   1717 21639 21651
"25468710" 500876 21639 21651
"25468710"  71755 21639 21654
"25468710" 119704 21639 21662
"25468710" 593008 21639 21664
"25468710" 581246 21639 21668
"25468710" 106603 21639 21668
"25468710" 593008 21639 21669
"25468710" 621768 21639 21668
"25468710" 507503 21639 21670
"25468710" 137702 21639 21675
"25468710" 564583 21639 21675
"25468710" 621768 21639 21675
"25468710"  46442 21639 21653
"25468710"   1717 21639 21677
"25468710"  54440 21730 21678
"25468710"  82890 21730 21678
"25468710"  46442 21730 21677
"25468710"  54310 21730 21678
"25468710" 137702 21730 21678
"25468710"   1717 21730 21678
"25468710" 595335 21730 21678
"25468710" 554250 21730 21678
"25468710" 500876 21730 21678
"25468710"   9584 21730 21678
"25468710" 581246 21730 21678
"25468710" 593008 21730 21678
"25468710" 507503 21730 21679
"25468710" 106603 21730 21678
"25468710" 564583 21730 21681
"25468710" 111523 21730 21677
"25468710" 119704 21730 21683
"25468710" 621768 21730 21684
"25468710"  71755 21730 21689
"25468710"  54440 21730 21702
"25468710" 535432 21730 21713
"25468710"   1717 21730 21714
"25468710"  71755 21730 21717
"25468710" 554250 21730 21727
"25468710"  54310 21730 21739
"25468710"  46442 21730 21740
"25468710" 137702 21730 21755
"25468710" 564583 21730 21761
"25468710" 500876 21730 21763
"25468710" 554250 21822 21767
"25468710"  54440 21822 21768
"25468710"   9584 21822 21768
"25468710"  82890 21822 21768
"25468710"  46442 21822 21767
"25468710" 119704 21822 21768
"25468710"  54310 21822 21768
"25468710" 564583 21822 21768
"25468710" 581246 21822 21768
"25468710" 137702 21822 21768
"25468710" 500876 21822 21768
"25468710"  54440 21822 21769
"25468710" 507503 21822 21770
"25468710" 106603 21822 21773
"25468710" 111523 21822 21767
"25468710"  71755 21822 21780
"25468710" 621768 21822 21780
"25468710"  71755 21822 21781
"25468710"   1717 21822 21791
"25468710"  46442 21822 21793
"25468710" 593008 21822 21803
"25468710" 119704 21822 21805
end
format %d fpedats
format %d anndats

[Edward Wu]

Up

[David Benson]

See if this helps:

gen qtr = quarter( fpedats)
gen year = year( fpedats)
bysort cusip year qtr: gen count_analys = _N  // number of analyst reports
gen days_before = fpedats - anndats  // how many days prior to qtr end was report issued
// Negative values means report AFTER quarter end
gsort cusip year qtr  -anndats  // put most recent reports first

table qtr, c(max n)

----------------------
      qtr |     max(n)
----------+-----------
        1 |         48
        2 |         29
        3 |         22
----------------------

. tabulate analys qtr

           |               qtr
    analys |         1          2          3 |     Total
-----------+---------------------------------+----------
      1717 |         3          2          1 |         6 
      9584 |         2          1          1 |         4 
     19083 |         1          0          0 |         1 
     46442 |         4          2          2 |         8 
     54310 |         1          2          1 |         4 
     54440 |         1          2          2 |         5 
     71755 |         4          2          2 |         8 
     82890 |         1          1          1 |         3 
    106603 |         2          1          1 |         4 
    111523 |         2          1          1 |         4 
    119704 |         1          1          2 |         4 
    121968 |         1          0          0 |         1 
    137702 |         2          2          1 |         5 
    500876 |         3          2          1 |         6 
    507503 |         2          1          1 |         4 
    535432 |         1          1          0 |         2 
    554250 |         2          2          1 |         5 
    564583 |         3          2          1 |         6 
    581246 |         2          1          1 |         4 
    582439 |         2          0          0 |         2 
    593008 |         3          1          1 |         5 
    595335 |         2          1          0 |         3 
    621768 |         3          1          1 |         5 
-----------+---------------------------------+----------
     Total |        48         29         22 |        99 


bysort cusip year qtr ( days_before): gen n = _n

list if n<=10, sepby( cusip year qtr) abbrev(12) noobs

  +------------------------------------------------------------------------------------------+
  |    cusip   analys     fpedats     anndats   qtr   year   count_analys   days_before    n |
  |------------------------------------------------------------------------------------------|
  | 25468710     1717   31mar2019   08may2019     1   2019             48           -38    1 |
  | 25468710   621768   31mar2019   06may2019     1   2019             48           -36    2 |
  | 25468710   137702   31mar2019   06may2019     1   2019             48           -36    3 |
  | 25468710   564583   31mar2019   06may2019     1   2019             48           -36    4 |
  | 25468710   507503   31mar2019   01may2019     1   2019             48           -31    5 |
  | 25468710   593008   31mar2019   30apr2019     1   2019             48           -30    6 |
  | 25468710   106603   31mar2019   29apr2019     1   2019             48           -29    7 |
  | 25468710   621768   31mar2019   29apr2019     1   2019             48           -29    8 |
  | 25468710   581246   31mar2019   29apr2019     1   2019             48           -29    9 |
  | 25468710   593008   31mar2019   25apr2019     1   2019             48           -25   10 |
  |------------------------------------------------------------------------------------------|
  | 25468710   500876   30jun2019   02aug2019     2   2019             29           -33    1 |
  | 25468710   564583   30jun2019   31jul2019     2   2019             29           -31    2 |
  | 25468710   137702   30jun2019   25jul2019     2   2019             29           -25    3 |
  | 25468710    46442   30jun2019   10jul2019     2   2019             29           -10    4 |
  | 25468710    54310   30jun2019   09jul2019     2   2019             29            -9    5 |
  | 25468710   554250   30jun2019   27jun2019     2   2019             29             3    6 |
  | 25468710    71755   30jun2019   17jun2019     2   2019             29            13    7 |
  | 25468710     1717   30jun2019   14jun2019     2   2019             29            16    8 |
  | 25468710   535432   30jun2019   13jun2019     2   2019             29            17    9 |
  | 25468710    54440   30jun2019   02jun2019     2   2019             29            28   10 |
  |------------------------------------------------------------------------------------------|
  | 25468710   119704   30sep2019   13sep2019     3   2019             22            17    1 |
  | 25468710   593008   30sep2019   11sep2019     3   2019             22            19    2 |
  | 25468710    46442   30sep2019   01sep2019     3   2019             22            29    3 |
  | 25468710     1717   30sep2019   30aug2019     3   2019             22            31    4 |
  | 25468710    71755   30sep2019   20aug2019     3   2019             22            41    5 |
  | 25468710    71755   30sep2019   19aug2019     3   2019             22            42    6 |
  | 25468710   621768   30sep2019   19aug2019     3   2019             22            42    7 |
  | 25468710   106603   30sep2019   12aug2019     3   2019             22            49    8 |
  | 25468710   507503   30sep2019   09aug2019     3   2019             22            52    9 |
  | 25468710    54440   30sep2019   08aug2019     3   2019             22            53   10 |
  +------------------------------------------------------------------------------------------+

// At this point could drop if count_analys <=2 or days_before < 0, etc