topic Re: looping over time in SAS Programming

looping over time

Sonyboy — Sat, 27 Feb 2016 01:27:35 GMT

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

Re: looping over time

Reeza — Sat, 27 Feb 2016 01:38:44 GMT

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.

Re: looping over time

Sonyboy — Sat, 27 Feb 2016 02:07:27 GMT

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;

Re: looping over time

Reeza — Sat, 27 Feb 2016 02:11:02 GMT

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.

Re: looping over time

Sonyboy — Sat, 27 Feb 2016 02:37:36 GMT

Dear Reeza,

Pls see the input and desired output files

I thank you in advance for your help

Re: looping over time

Reeza — Sat, 27 Feb 2016 02:44:43 GMT

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;

Re: looping over time

Sonyboy — Sat, 27 Feb 2016 10:59:13 GMT

Reeza,

thank you for your time. I appreciate this.

It worked:-)

Re: looping over time

data_null__ — Sat, 27 Feb 2016 12:08:58 GMT

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;