Help using Base SAS procedures

How do I calculate cumulative average for past 30 mins

Reply
N/A
Posts: 0

How do I calculate cumulative average for past 30 mins

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;
Super User
Posts: 5,260

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

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
Data never sleeps
Ask a Question
Discussion stats
  • 1 reply
  • 140 views
  • 0 likes
  • 2 in conversation