topic recursive mean error in SAS Procedures

recursive mean error

sasphd — Mon, 28 Sep 2020 20:08:00 GMT

hello,

I did not find my error in this program. there is no error in the log but the output did not do any calculation

data have;
input code DATe : yymmdd10. variation;* recursive mean;
format date yymmdd10.;
cards;
1 2020-01-02 -1.35
1 2020-01-03 -1.48
1 2020-01-06 2.40
1 2020-01-07 -0.85
1 2020-01-08 -0.24
1 2020-01-09 2.49
1 2020-01-10 1.68
1 2020-01-13 -1.69
1 2020-01-14 -0.91
1 2020-01-15 -0.93
1 2020-01-16 2.71
1 2020-01-17 -0.05
1 2020-01-20 1.69
1 2020-01-21 -3.64
1 2020-01-22 1.24
1 2020-01-23 -2.05
1 2020-01-24 -2.97
1 2020-01-27 -0.78
1 2020-01-28 3.09;

proc sql;
create table want as
select a.code,a.date,a.variation,ifn(count(*)=11,mean(b.variation),.) as rec_mean format=8.4
from ind a left join ind b

on a.code=b.code and intnx('day',a.DATE,-12)<= b.DATE<=intnx('day',a.DATE,-2)
group by a.code,a.date,a.variation
order by a.code,a.date;
quit;

Re: recursive mean error

sasphd — Mon, 28 Sep 2020 20:09:15 GMT

hello,

I did not find my error in this program. there is no error in the log but the output did not do any calculation

data ind;
input code DATe : yymmdd10. variation;* recursive mean;
format date yymmdd10.;
cards;
1 2020-01-02 -1.35
1 2020-01-03 -1.48
1 2020-01-06 2.40
1 2020-01-07 -0.85
1 2020-01-08 -0.24
1 2020-01-09 2.49
1 2020-01-10 1.68
1 2020-01-13 -1.69
1 2020-01-14 -0.91
1 2020-01-15 -0.93
1 2020-01-16 2.71
1 2020-01-17 -0.05
1 2020-01-20 1.69
1 2020-01-21 -3.64
1 2020-01-22 1.24
1 2020-01-23 -2.05
1 2020-01-24 -2.97
1 2020-01-27 -0.78
1 2020-01-28 3.09;

proc sql;
create table want as
select a.code,a.date,a.variation,ifn(count(*)=11,mean(b.variation),.) as rec_mean format=8.4
from ind a left join ind b

on a.code=b.code and intnx('day',a.DATE,-12)<= b.DATE<=intnx('day',a.DATE,-2)
group by a.code,a.date,a.variation
order by a.code,a.date;
quit;

Re: recursive mean error

Reeza — Mon, 28 Sep 2020 20:13:16 GMT

How does IFN() work in SQL? Would it work on the aggregated data or the row level data?

I'm going to guess that's the issue. I would recommend a data step instead or PROC EXPAND if you have the SAS/ETS license.

data want;
array p{0:3} _temporary_;
set have;
by object;
if first.object then do; call missing(of p{*}); count=0; end;
count+1;
p{mod(_n_,4)} = price;
if count >4 then lowest = min(of p{*});
if count > 4 then highest = max(of p{*});
run;

Re: recursive mean error

sasphd — Mon, 28 Sep 2020 20:47:01 GMT

thanks reeza

but INF works here!!!!!

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;