Loop query

[ctse]

Hi,

I am trying to do a loop in SAS but I am not quite sure how that works. I have prices recorded over time from 8.30am to 3.05pm on a daily basis for five years and want to estimate a rolling window with a one-minute interval. For example, I want to estimate average of prices each day from 8.31am to 2.06pm. The next window is going to be from 8.32am to 2.07pm and so on until the last window which is from 9.30am to 3.05pm.

The loop starts from {i} = 1 to 60.

I have written this code below and have been trying to trying to replace {i} with the numbers in the loop.

NOTE: I am also not sure about this line in the code too: "where myTIME <= '14:05:00't + minute{i};" . I am trying to put a limit to tell SAS where I want it to end each day and to increment that ending time by 1 minute each time.


thanks
c+

****************************************
data work.{i}test;
set work.source;
by myDay;
retain ORIGINDatetime;
FIRSTOBS = first.myDay;
if FIRSTOBS then do;
ORIGINDatetime = myDateTime + (60 * {i});
end;
format ORIGINDatetime datetime.;
run;

data work.{i}test01;
set work.{i}test;
where myTIME <= '14:05:00't + minute{i};
run;

proc sql;
create table work.{i}test02 as
select myDay,mean(price) as avg_price
from work.{i}test01
group by myDay;
quit;

 

[klaz2002]

First off you must understand SAS's data step. The data step applies an implicit loop when called. This means that each observation is independant and ARRAY logic is not done on many observations but on a single observation (by default).

This may sound like techineese but it will mean something as we go on. In your case you need to calculate a subset of daily records to arrive at your average.

Why not run a PROC Means on your data 60 times. Each time you will tell your code how to subset your data by time. The last step in your MACRO would be to append the stats to a dataset. While this may take longer to run, who cares how long a computer runs as long as its reasonable.

If you need an example let me know.

Klaz