DATA Step, Macro, Functions and more

looping over time

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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: 17,963

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 Smiley Happy

 

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;

 

 

View solution in original post


All Replies
Super User
Posts: 17,963

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: 17,963

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: 17,963

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 Smiley Happy

 

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:-)

 

Respected Advisor
Posts: 3,777

Re: looping over time

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;

Capture.PNG

☑ This topic is solved.

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

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