Announcement

Collapse
No announcement yet.
X
  • Filter
  • Time
  • Show
Clear All
new posts

  • How can I create a lagged 4 year max variable?

    The relevant column in my dataset are "gvkey" (a group id), datadate (ex. YYYYMMDD = 20100531), and revt.

    The observation frequency is annual.
    I want to to construct a rolling max of "revt" for the PREVIOUS 4 years for some "gvkey".
    I don't want to hard code a window size of 5 because this would not handle missing values appropriately.

    I'm very new the STATA so I'm not sure how this should be handled but I think SQL is the way to go.

    For sake of clarity, I need something that is the functional equivalent in STATA to this code from SAS

    proc sql;
    create table want as select
    a.gvkey,
    a.datadate,
    max(b.revt) as maxRevt
    from
    have as a inner join
    have as b on a.gvkey=b.gvkey and
    b.datadate between intnx("YEAR", a.datadate, -4) and a.datadate - 1
    group by a.gvkey, a.datadate;
    quit;
    Last edited by Logan Nielsen; 18 Sep 2020, 07:08.

  • #2
    I do not know neither SAS nor SQL, and I dont know what "to hard code a window size of 5 " means. But if I understand correctly what you want, it can be done as below. And pseudo code which has not been checked follows, because you have not provided a data sample with -dataex-.

    Code:
    xtset gvkey datadate
    
    gen maxlastfour = max(l.revt, l2.revt, l3.revt, l4.revt)
    Last edited by Joro Kolev; 18 Sep 2020, 11:11.

    Comment

    Working...
    X