recursive mean

[sasphd]

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

[PeterClemmensen]

Do you have a SAS ETS license?

[sasphd]

yes

[PaigeMiller]

You can do these calculations in PROC EXPAND.

[sasphd]

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;

[PaigeMiller]

Here is an example of a moving average of 3.

https://documentation.sas.com/?cdcId=pgmsascdc&cdcVersion=9.4_3.4&docsetId=etsug&docsetTarget=etsug_...

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.

[sasphd]

I am not sure to understand the example can you please give me the program using my example

[PaigeMiller]

Give it a try. If you get stuck, show us the code you tried that isn't working.

[PeterClemmensen]

Are the posted values your actual desired data?

[ed_sas_member · Accepted solution]

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;

[sasphd]

it works 🙂 

thanks to everybody

[novinosrin]

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;

[novinosrin]

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;