BookmarkSubscribeRSS Feed
sasphd
Lapis Lazuli | Level 10

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;

 

3 REPLIES 3
sasphd
Lapis Lazuli | Level 10
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;

Reeza
Super User
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;
sasphd
Lapis Lazuli | Level 10

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;

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 251 views
  • 0 likes
  • 2 in conversation