Hi,
I want to calculate mean from t-2 to t-12 recursively for each compagny (GVKEY)
GVKEY | DATADATE | TRT1M | recursive mean |
001081 | 2004-01-31 | -1.8322 | |
001081 | 2004-02-29 | 4.3713 | |
001081 | 2004-03-31 | -11.9811 | |
001081 | 2004-04-30 | 2.8939 | |
001081 | 2004-05-31 | 2.2396 | |
001081 | 2004-06-30 | -6.5373 | |
001081 | 2004-07-31 | -4.6448 | |
001081 | 2004-08-31 | -10.4598 | |
001081 | 2004-09-30 | 2.0539 | |
001081 | 2004-10-31 | -8.2390 | |
001081 | 2004-11-30 | 10.0413 | |
001081 | 2004-12-31 | 3.2500 | |
001081 | 2005-01-31 | -18.2809 | -2.01 |
001081 | 2005-02-28 | -14.8889 | -1.55 |
001081 | 2005-03-31 | -2.0979 | -3.61 |
001096 | 2004-01-31 | -4.0816 | |
001096 | 2004-02-29 | 4.6809 | |
001096 | 2004-03-31 | -2.8862 | |
001096 | 2004-04-30 | -3.1579 | |
001096 | 2004-05-31 | -1.0870 | |
001096 | 2004-06-30 | 2.8132 | |
001096 | 2004-07-31 | -3.2258 | |
001096 | 2004-08-31 | 3.3333 | |
001096 | 2004-09-30 | 4.9032 | |
001096 | 2004-10-31 | 1.6495 | |
001096 | 2004-11-30 | 5.4767 | |
001096 | 2004-12-31 | 0.7308 | |
001096 | 2005-01-31 | 1.7274 | 0.77 |
001096 | 2005-02-28 | 2.0755 | 1.20 |
Hi @sasphd
You can try this:
data test;
set have;
format rec_mean 8.2;
by GVKEY;
if first.GVKEY then counter = 0;
counter+1;
rec_mean = mean(lag2(TRT1M),lag3(TRT1M),lag4(TRT1M),lag5(TRT1M),
lag6(TRT1M),lag7(TRT1M),lag8(TRT1M),lag9(TRT1M),
lag10(TRT1M),lag11(TRT1M),lag12(TRT1M));
if counter <= 12 then call missing(rec_mean);
drop counter;
run;
Do you have a SAS ETS license?
yes
You can do these calculations in PROC EXPAND.
how ??? can you please give the program
data test;
input GVKEY $ DATADATe $ TRT1M $ mean$;
datalines;
001081 2004-01-31 -1.8322 .
001081 2004-02-29 4.3713 .
001081 2004-03-31 -11.9811 .
001081 2004-04-30 2.8939 .
001081 2004-05-31 2.2396 .
001081 2004-06-30 -6.5373 .
001081 2004-07-31 -4.6448 .
001081 2004-08-31 -10.4598 .
001081 2004-09-30 2.0539 .
001081 2004-10-31 -8.2390 .
001081 2004-11-30 10.0413 .
001081 2004-12-31 3.2500 .
001081 2005-01-31 -18.2809 -2.01
001081 2005-02-28 -14.8889 -1.55
001081 2005-03-31 -2.0979 -3.61
001096 2004-01-31 -4.0816 .
001096 2004-02-29 4.6809 .
001096 2004-03-31 -2.8862 .
001096 2004-04-30 -3.1579 .
001096 2004-05-31 -1.0870 .
001096 2004-06-30 2.8132 .
001096 2004-07-31 -3.2258 .
001096 2004-08-31 3.3333 .
001096 2004-09-30 4.9032 .
001096 2004-10-31 1.6495 .
001096 2004-11-30 5.4767 .
001096 2004-12-31 0.7308 .
001096 2005-01-31 1.7274 0.77
001096 2005-02-28 2.0755 1.20
run;
Here is an example of a moving average of 3.
Since you want a moving average of 12, you'd have to make the obvious modification to the code, and then set to missing any means you don't want.
I am not sure to understand the example can you please give me the program using my example
Give it a try. If you get stuck, show us the code you tried that isn't working.
Are the posted values your actual desired data?
Hi @sasphd
You can try this:
data test;
set have;
format rec_mean 8.2;
by GVKEY;
if first.GVKEY then counter = 0;
counter+1;
rec_mean = mean(lag2(TRT1M),lag3(TRT1M),lag4(TRT1M),lag5(TRT1M),
lag6(TRT1M),lag7(TRT1M),lag8(TRT1M),lag9(TRT1M),
lag10(TRT1M),lag11(TRT1M),lag12(TRT1M));
if counter <= 12 then call missing(rec_mean);
drop counter;
run;
it works 🙂
thanks to everybody
data have;
input GVKEY DATADATE : yymmdd10. TRT1M;* recursive mean;
format datadate yymmdd10.;
cards;
001081 2004-01-31 -1.8322
001081 2004-02-29 4.3713
001081 2004-03-31 -11.9811
001081 2004-04-30 2.8939
001081 2004-05-31 2.2396
001081 2004-06-30 -6.5373
001081 2004-07-31 -4.6448
001081 2004-08-31 -10.4598
001081 2004-09-30 2.0539
001081 2004-10-31 -8.2390
001081 2004-11-30 10.0413
001081 2004-12-31 3.2500
001081 2005-01-31 -18.2809 -2.01
001081 2005-02-28 -14.8889 -1.55
001081 2005-03-31 -2.0979 -3.61
001096 2004-01-31 -4.0816
001096 2004-02-29 4.6809
001096 2004-03-31 -2.8862
001096 2004-04-30 -3.1579
001096 2004-05-31 -1.0870
001096 2004-06-30 2.8132
001096 2004-07-31 -3.2258
001096 2004-08-31 3.3333
001096 2004-09-30 4.9032
001096 2004-10-31 1.6495
001096 2004-11-30 5.4767
001096 2004-12-31 0.7308
001096 2005-01-31 1.7274 0.77
001096 2005-02-28 2.0755 1.20
;
proc sql;
create table want as
select a.gvkey,a.datadate,a.TRT1M,ifn(count(*)=11,mean(b.TRT1M),.) as rec_mean format=8.2
from have a left join have b
on a.gvkey=b.gvkey and intnx('mon',a.DATADATE,-12,'e')<= b.DATADATE<=intnx('mon',a.DATADATE,-2,'e')
group by a.gvkey,a.datadate,a.TRT1M
order by a.gvkey,a.datadate;
quit;
Had some free time during lunch break for some HASH fun 🙂
data have;
input GVKEY DATADATE : yymmdd10. TRT1M;* recursive mean;
format datadate yymmdd10.;
cards;
001081 2004-01-31 -1.8322
001081 2004-02-29 4.3713
001081 2004-03-31 -11.9811
001081 2004-04-30 2.8939
001081 2004-05-31 2.2396
001081 2004-06-30 -6.5373
001081 2004-07-31 -4.6448
001081 2004-08-31 -10.4598
001081 2004-09-30 2.0539
001081 2004-10-31 -8.2390
001081 2004-11-30 10.0413
001081 2004-12-31 3.2500
001081 2005-01-31 -18.2809 -2.01
001081 2005-02-28 -14.8889 -1.55
001081 2005-03-31 -2.0979 -3.61
001096 2004-01-31 -4.0816
001096 2004-02-29 4.6809
001096 2004-03-31 -2.8862
001096 2004-04-30 -3.1579
001096 2004-05-31 -1.0870
001096 2004-06-30 2.8132
001096 2004-07-31 -3.2258
001096 2004-08-31 3.3333
001096 2004-09-30 4.9032
001096 2004-10-31 1.6495
001096 2004-11-30 5.4767
001096 2004-12-31 0.7308
001096 2005-01-31 1.7274 0.77
001096 2005-02-28 2.0755 1.20
;
data want;
if _n_=1 then do;
dcl hash H () ;
h.definekey ("datadate") ;
h.definedata ("rec_mean") ;
h.definedone () ;
end;
do _n_=1 by 1 until(last.gvkey);
set have;
by gvkey;
array t(0:10) _temporary_;
t(mod(_n_,11))=TRT1M;
if n(of t(*))=11 then do;
rec_mean=mean(of t(*));
h.add(key:intnx('mon',datadate,2,'e'),data:rec_mean);
end;
if h.find() ne 0 then call missing(rec_mean);
output;
end;
h.clear();
call missing(of t(*));
format rec_mean 8.2;
run;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.
Find more tutorials on the SAS Users YouTube channel.