grouping by 10 min time windows

[PaigeMiller]

Wait! I just thought of a way to do this without macros or CALL EXECUTE. You could use the CNTLIN= option in PROC FORMAT, but I don't really have the time to work out the code right now.

I always forget about the CNTLIN= option. I hate it when that happens.

[FreelanceReinh]

Combining all the good suggestions:

data have;
input time :time. books;
format time time5.;
cards;
16:01 3
16:02 5
16:03 4
16:09 3
16:11 2
16:12 5
16:55 8
;

data tfmt / view=tfmt;
fmtname='timewin';
do start='0:01't to '23:51't by '0:10't;
  end=start+'0:10't;
  eexcl='Y';
  label=put(start,time5.)||' - '||put(end-1,time5.);
  output;
end;
proc format cntlin=tfmt;
run;

proc sql;
create table want as
select put(time, timewin.) as timewin label='Time',
       avg(books) as avg_books label='Avg(Books)'
from have
group by timewin;
quit;

proc print data=want label;
format avg_books 10.2;
run;

(Edit: Extended the ranges of the format to avoid gaps between them.

Edit 2: Restored code highlighting and indentation [bug in forum software?])

[data_null__]

INTNX

data time;
   input Time:time.  Books;
   g1 = intnx('MINUTE10.2',time,0,'B');
   g2 = intnx('MINUTE10.2',time,0,'E');
   format time g: time5.;
   cards;
16:01  3
16:02  5
16:03  4
16:09  3
16:11  2
16:12  5
16:55  8
;;;;
   run;
proc print;
   run;
proc summary nway;
   class g:;
   output out=avg10 mean(books)=avgbooks;
   run;
proc print;
   run;

[novinosrin]

@data_null__ Brilliance personified. I never really properly understood the computation of intervals like interval.x MINUTE10.2 although I have readthe doc a few times. 

[data_null__]

Brilliance no. More like trial and error.

[ballardw]

---

@data_null__ wrote:
Brilliance no. More like trial and error.

---

And I suspect a certain amount of persistence.

[novinosrin]

for what it's worth, my fun part

data have;
input Time :time5.  Books;
format time time5.;
cards;
16:01  3
16:02  5
16:03  4
16:09  3
16:11  2
16:12  5
16:55  8
;

data temp;
do time=60 to 86400 by 60 ;
if mod(minute(time),10)=1 then grp+1;
output;
end;
format time time5.;
run;

proc sql;
create table want as
select   catx('-',put(min(a.time),time5.),put(max(a.time),time5.)) as time,mean(books) as avg 
from temp a left join have b
on a.time=b.time
group by grp
having avg ne .
order by grp,time;
quit;

[Ksharp]

data have;
input Time : time5. Books;
format time hhmm5.;
cards;
16:01  3
16:02  5
16:03  4
16:09  3
16:11  2
16:12  5
16:55  8
;
run;

data temp;
 set have;
 retain temp;
 if _n_=1 then temp=time;
 if time>temp+'00:09:00't then do;group+1;temp=time;end;
 drop temp;
run;

proc sql;
create table want as
 select catx(' - ',put(min(time),hhmm5.),put(min(time)+'00:09:00't,hhmm5.)) as time,
 mean(books) as avg_books
  from temp
   group by group;
quit;