How do I calculate cumulative average for past 30 mins

deleted_user — Tue, 25 May 2010 05:47:10 GMT

Hello,
I have a dataset with variable TICODE(code of the product),TRADETSTMP(time recorded) ,UNIQ_KEY(unique number for the record) and RETURN(value for that unique key)..I need to develop a code using Base SAS to generate average of RETURN of all observations in last 30 mins (moving average)..I do have a code which is doing the work for me but am not sure if its the ideal or optimised code...I request u to kindly suggest me a better more optimised code for the same
Here is my code :-

libname ts 'd:\Priyank\SASDatasets';
data ts.samp;
set ts.gb0030738610;
where ticode = "GB0030738610";
if return=. then return=0;
rename uniq_key=uniq_key1;
run;
data ts.samp1;
set ts.gb0030738610;
where ticode = "GB0030738610";
if lag(TRADETSTMP) ^=. then do;
do i=1800 to 0 by -1;
dt=intnx('second',TRADETSTMP,-i,'same');
output;
end;
end;
else output;
format dt datetime.;
drop return;
run;
proc sql;
create table ts.samp2 as
select a.* , b.return, b.uniq_key1 from ts.samp1 as a left join ts.samp as b on a.ticode=b.ticode and a.dt=b.TRADETSTMP
order by TICODE,TRADETSTMP,DATE,TIME,Price,UNIQ_KEy;
quit;
data ts.samp2;
set ts.samp2;
if dt=. and i=. then return=0;
if ((dt=. and i=. and UNIQ_KEy1=.) or UNIQ_KEy1^=. )and uniq_key1 <= uniq_key then output;
run;
data ts.samp3;
set ts.samp2;
ct=1;
if return ^=. then output;
/*drop dt i;*/
run;

proc sql;
create table ts.samp4 as
select TICODE,TRADETSTMP,DATE,TIME,Price,UNIQ_KEy, sum(ct) as ct, sum(return)/sum(ct) as avg format=8.5
from ts.samp3
group by TICODE,TRADETSTMP,DATE,TIME,Price,UNIQ_KEy ;
quit;

Re: How do I calculate cumulative average for past 30 mins

LinusH — Tue, 25 May 2010 10:34:30 GMT

It seems that your code does more calculations than the text describes.
It's hard to give optimization tips when you don't know all the rules.

/Linus

topic How do I calculate cumulative average for past 30 mins in SAS Procedures

How do I calculate cumulative average for past 30 mins

Re: How do I calculate cumulative average for past 30 mins