The last option is the only one that worked. The ones with the `ntnx` command produced 0 observation tables. Why would that be? Also for the last one, how does it know to subtract from the years? As far as I understand it views 20100531 as a big integer Proc sql;
create table final as
select
a.gvkey,
a.datadate,
max(b.revt) as maxRevt
from
data as a inner join
data 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;
... View more
Proc sql;
create table final as
select
a.gvkey,
a.datadate,
max(b.revt) as maxRevt
from
data as a inner join
data as b on a.gvkey=b.gvkey and
b.datadate between intnx("YEAR", a.datadate, -4) and intnx("YEAR", a.datadate, -1)
group by a.gvkey, a.datadate;
quit; Figured out how to insert code haha.
... View more
So I'd like to only look at the previous 5 years and exclude the current observation. I thought that something like this might work ``` b.datadate between intnx("YEAR", a.datadate, -4) and intnx("YEAR", a.datadate, -1) ```` ^^^ All I did was modify a.datadate to intnx("YEAR", a.datadate, -1) which it doesn't like...
... View more
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 SAS 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's a little like ```data['5yr_revt_max'] = data.groupby("gvkey")['revt'].shift(1).rolling(5).max()``` Except this doesn't account for the years and is in Python
... View more