## looping over time

Solved
Occasional Contributor
Posts: 17

# looping over time

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

Accepted Solutions
Solution
‎02-27-2016 05:58 AM
Super User
Posts: 23,776

## Re: looping over time

[ Edited ]

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;``````

All Replies
Super User
Posts: 23,776

## Re: looping over time

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.

Occasional Contributor
Posts: 17

## Re: looping over time

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;

Super User
Posts: 23,776

## Re: looping over time

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.

Occasional Contributor
Posts: 17

## Re: looping over time

Dear Reeza,

Pls see the input and desired output files

I thank you in advance for your help

Solution
‎02-27-2016 05:58 AM
Super User
Posts: 23,776

## Re: looping over time

[ Edited ]

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;``````

Occasional Contributor
Posts: 17

## Re: looping over time

Reeza,

thank you for your time. I appreciate this.

It worked:-)

Posts: 3,852

## Re: looping over time

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

``````data have;
infile cards dsd dlm='09'x;
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;
run;
ods select all;
proc print;
run;
``````

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
• 7 replies
• 778 views
• 1 like
• 3 in conversation