looping over time

[Sonyboy]

Dear all;

a beginner question

How can I create a loop that will select the data for five minute intervals...

Basically, I want the program to choose the five minutes intervals between 1530 and 22:00.

Said differently, I would like the program to choose 15:30-15:35, 15:35-15:40 ,so forth untl 22:00,,, 

then within that loop I would like to make calculations for the subset,...then output them,

also for each interval, is it possible to use the proc procedures like proc mean, proc reg, etc

thanks 

Sony

[Reeza]

What do you mean by select data? 

If if you need to run Procs in each loop you will need a macro loop. 

However its its highly likely that BY processing will be more efficient. 

I suggest posting more details and an example of what you're trying to achieve. 

[Sonyboy]

Reeza,

I am a beginner, therefore not sure I can explain myself clearly. What I need is that the loop makes calculations for each of the 5 minutes intervals in a time horizon. For each interval, i need to calculate the ratio of max/min and see these values separetely...

How would a macro look like?

Below is a failed example of a code?

data b;
do i='15:30:00't to '22:00:00't by '00:05:00't;
do j=1 to 79;
data a;
set basefile;
keep date time midprice;
where time>i and time< (i+'00:05:00't);
proc means data=a noprint ;
var midprice;
by date ;
output out=a1;
run;
data a2;
set a1;
if _STAT_="MIN" ;
rename midprice=minR;
run;
data a3;
set a1;
if _STAT_="MAX" ;
rename midprice=maxR;
run;
end;
end;
run;

[Reeza]

You don't need a macro, round time to nearest 5 mins and run proc means with by group. 

Post what your data looks like and what you want as output. 

[Sonyboy]

Dear Reeza,

Pls see the input and desired output files

I thank you in advance for your help

[Reeza · Accepted solution]

Here's a sketch - untested. It most likely won't work as is, but you should be able to get the idea and modify the code as required.

SAS stores dates as the number of days from January 1, 1960 and datetime as number of seconds from January 1, 1960. This means you can treat it as a number and do math with it 🙂

data temp;
set have;
datetime=date*60*60*24+time; *Convert to datetime;
datetime_round=floor(datetime/300); *round to 5 minute intervals;

format datetime_round datetime21.;
run;

proc means data=temp noprint nway;
class datetime_round;
var price;
output out=temp2 min(price)=min_price max(price)=max_price;
run;

data want;
set temp2;
Ratio=max/min;
run;

[Sonyboy]

Reeza,

thank you for your time. I appreciate this.

It worked:-)

[data_null__]

Tested using, using SAS functions for date-time variable.

data have;
   infile cards dsd dlm='09'x;
   input date:Date.	time:time.	price_bid	price_ask	midprice	spread	relative_spread;
   format date date9. time time12.;
   dt  = dhms(date,0,0,time);
   dt5 = intnx('minute5',dt,0,'B');
   format dt dt5 datetime21.;
   cards;
02Apr2012	0:00:00	45.06248623	48.9866028	47.02454451	3.924116572	0.083448263
02Apr2012	0:01:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:02:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:03:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:04:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:05:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:06:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:07:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:08:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:09:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:10:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
02Apr2012	0:11:00	45.74726852	46.2597066	46.00348756	0.512438082	0.011139114
;;;;
   run;
proc print;
   run;
ods select none;
ods output summary=summary;
proc means stackods min max;
   class dt5;
   var price_bid--relative_spread;
   run;
ods select all;
proc print;
   run;