topic Re: grouping by 10 min time windows in SAS Programming

grouping by 10 min time windows

Anirudh9 — Fri, 21 Sep 2018 15:03:17 GMT

I have a table that looks like this

Time Books

16:01 3

16:02 5

16:03 4

16:09 3

16:11 2

16:12 5

16:55 8

I want to group by 10 min time windows so that I get a table that looks like this

Time Avg(Books)

16:01 - 16:10 3.75

16:11 - 16:20 3.5

16:51 - 17:00 8

Re: grouping by 10 min time windows

novinosrin — Fri, 21 Sep 2018 15:48:54 GMT

Do you have continuous data for the 10 minute windows for your entire dataset?

meaning 11-20,21-20 and so on unlike a break after16:12 --16:55

Re: grouping by 10 min time windows

Anirudh9 — Fri, 21 Sep 2018 15:50:36 GMT

There are breaks like the one you pointed out.

Re: grouping by 10 min time windows

novinosrin — Fri, 21 Sep 2018 15:55:02 GMT

Ok I do have solution in my mind but let me wait for somebody to give a robust solution who does well in times series. I suppose they may even suggest you SAS ETS procs like proc expand and proc timeseries or even a nice datastep solution. hang in there.

Re: grouping by 10 min time windows

Anirudh9 — Fri, 21 Sep 2018 15:56:29 GMT

Thank you!!

Can you help me with this?

https://communities.sas.com/t5/SAS-Programming/Creating-TimeStamp-with-Hour-and-Minute-Values/m-p/497883#M132136

Re: grouping by 10 min time windows

pink_poodle — Fri, 21 Sep 2018 16:19:35 GMT

You will need to convert times to SAS values using the HMS(hour, minute, second) function.

1) Create user-defined format $time :

PROC FORMAT;

VALUE time;

HMS(16, 1, 0) - HMS(16, 10, 0) = "16:01 - 16:10"

HMS(16, 11, 0) - HMS(16, 20, 0) = "16:11 - 16:20"

HMS(16, 51, 0) - HMS(17, 0, 0) = "16:51 - 17:00" ;

RUN;

2) Group by formatted values using PROC SQL (https://communities.sas.com/t5/SAS-Enterprise-Guide/Proc-SQL-Group-By-Formatted-Values/td-p/187278) :

PROC SQL,

SELECT put(Time, $time.) AS Time, avg(Books)

FROM dataset

GROUP BY time;

quit;

Re: grouping by 10 min time windows

PaigeMiller — Fri, 21 Sep 2018 17:13:26 GMT

The HMS function does not work inside of PROC FORMAT.

It seems to me that the solution to the original problem may require some macro coding and looping to create the formats properly (or alternatively CALL EXECUTE could do this as well). I cannot think of a way to create the formats without some sort of macro or CALL EXECUTE. Once the format is created properly, then PROC SUMMARY will give you the results very easily.

Re: grouping by 10 min time windows

PaigeMiller — Fri, 21 Sep 2018 17:23:25 GMT

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.

Re: grouping by 10 min time windows

FreelanceReinh — Fri, 21 Sep 2018 20:14:25 GMT

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?])

Re: grouping by 10 min time windows

data_null__ — Fri, 21 Sep 2018 17:44:50 GMT

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;

Re: grouping by 10 min time windows

novinosrin — Fri, 21 Sep 2018 17:56:05 GMT

@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.

Re: grouping by 10 min time windows

data_null__ — Fri, 21 Sep 2018 18:00:36 GMT

Brilliance no. More like trial and error.

Re: grouping by 10 min time windows

FreelanceReinh — Fri, 21 Sep 2018 22:48:55 GMT

@novinosrin wrote:

... or even a nice datastep solution. (...)

data want(keep=timewin avg_books rename=(timewin=Time));
do n=1 by 1 until(eof | time>=w+600);
  set have end=eof;
  w=round(time-360,600)+60;
  timewin=put(w,time5.)||' - '||put(w+540,time5.);
  s=sum(s,books);
  if ~eof then set have(firstobs=2 keep=time);
end;
Avg_Books=s/n;
run;

Not sure if it's "nice", but it's a datastep solution.

(Edit: Minor simplification: Variable TIME in the second SET statement doesn't need to be renamed. Its value is used in the UNTIL condition.)

Re: grouping by 10 min time windows

novinosrin — Fri, 21 Sep 2018 21:26:24 GMT

Sir @FreelanceReinh Marvelous and yet another golf feather. It took me a while to understand the genie in that code and certainly not the novice/mid level. So to be fair, and not knowing OP's level and for the benefit of wider audience,may i request you to add a couple of notes if you don't mind at your own convenience plz

Thank you & Can't appreciate enough

Re: grouping by 10 min time windows

ballardw — Fri, 21 Sep 2018 21:47:16 GMT

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

And I suspect a certain amount of persistence.

Re: grouping by 10 min time windows

FreelanceReinh — Fri, 21 Sep 2018 23:45:07 GMT

Thanks, @novinosrin.

The process is as follows:

Variable N is set to 1.
The first SET statement reads the first or (in subsequent iterations of the data step) the next observation from dataset HAVE.
Determine the time window of the TIME value just read. W is the lower limit of that interval. Example: TIME='16:03't, this minus 6 minutes (360 s) is '15:57't, rounded to 10 minutes is '16:00't, plus 60 s gives '16:01't.
Construct the display value of the time window. (Upper limit is lower limit plus 9*60 s.)
Add current value of BOOKS to variable S. The SUM function has two nice properties: It treats missing values of S (e.g. in the very first call) as zeros (unlike s=s+books;) and it doesn't imply a RETAIN (as the sum statement, s+books;, would).
If the last observation of HAVE hasn't been reached yet (end-of-file variable EOF=0, hence condition "not EOF" is true), look ahead into the next observation of HAVE and retrieve its TIME value.* Thanks to the FIRSTOBS=2 option the second SET statement is always "one observation ahead". The KEEP= option has only performance reasons here. It's not mandatory.
At the bottom of the DO-UNTIL loop the UNTIL condition is checked: If the very last observation (EOF=1) or the last observation of the current time window (criterion: the TIME value of the next obs. belongs to a later time window, i.e. is greater than or equal to the left endpoint of the current window plus 10 min) has been reached, the loop terminates and the accumulated number of books, S, is divided by the corresponding number of observations, N (which had been incremented "by 1" in each iteration of the loop). The result is stored in variable Avg_Books and written to dataset WANT by the implied OUTPUT statement of the data step, together with variable TIMEWIN, renamed to Time for "cosmetic" reasons. Else, if the UNTIL condition is not met, we continue at step 2 above with N incremented by 1.
At the beginning of the next iteration of the data step, i.e., immediately before we start over at step 1 above, variable S (and all other non-retained variables) is automatically set to missing, as usual. Thus, none of the cumulative values is carried over from one time window to the next. If the last observation of HAVE was reached in the previous iteration, the data step is terminated as soon as the first SET statement is executed again. Without the IF condition "~eof" the second SET statement would terminate the datastep prematurely because it's "one observation ahead".

*I realize now that we may overwrite TIME, i.e., renaming was unnecessary (have updated the code).

Re: grouping by 10 min time windows

novinosrin — Fri, 21 Sep 2018 23:29:49 GMT

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;

Re: grouping by 10 min time windows

novinosrin — Fri, 21 Sep 2018 23:35:40 GMT

Merci (Thank you very much= Merci Beaucoup) 🙂

Re: grouping by 10 min time windows

Ksharp — Sat, 22 Sep 2018 11:55:19 GMT

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;

Re: grouping by 10 min time windows

PGStats — Fri, 28 Sep 2018 02:06:14 GMT

Make it even "nicer" with time literals:

data want(keep=timewin avg_books rename=(timewin=Time));
do n=1 by 1 until(eof or time >= w + '00:10:00't);
  set have end=eof;
  w=round(time - '00:06:00't, '00:10:00't) + '00:01:00't;
  timewin=put(w, time5.)||' - '||put(w + '00:09:00't, time5.);
  s=sum(s, books);
  if not eof then set have(firstobs=2 keep=time);
end;
Avg_Books=s/n;
run;