How do I create a rolling average on annual data?

Reply
Contributor
Posts: 23

How do I create a rolling average on annual data?

I am trying to create a variable that equals the average of free cash flow (fcf) for years t-2 through t-4 for each firm (cusip).  This code works beautifully on my quarterly dataset, but I cannot figure out the tweak to make it work on my annual dataset.  It returns "." for each observation. Any help would be lovely!!!

 

I am using the code: 

 

proc sql;
create table rollingavg_fcf as
select *,(select mean(fcf) from trial where year between intnx('year',a.year,-4) and intnx('year',a.year,-2) and cusip=a.cusip) as rollingavg_fcf
from trial as a;
quit;
run;

Super User
Super User
Posts: 7,400

Re: How do I create a rolling average on annual data?

Hi,

 

Maybe start with a search on the forums, there is some 50 topics which come up for rolling average:

https://communities.sas.com/t5/forums/searchpage/tab/message?q=rolling+average

 

Super User
Posts: 6,932

Re: How do I create a rolling average on annual data?

If year is not a SAS date, but a numerical value like 2016, you can't (reasonably) apply the intnx function to it.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 23

Re: How do I create a rolling average on annual data?

Ah, and that is exactly where the problem lies. Thank you, Kurt.

Ask a Question
Discussion stats
  • 3 replies
  • 180 views
  • 0 likes
  • 3 in conversation