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
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
There are breaks like the one you pointed out.
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.
Thank you!!
Can you help me with this?
@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.)
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
Thanks, @novinosrin.
The process is as follows:
*I realize now that we may overwrite TIME, i.e., renaming was unnecessary (have updated the code).
Merci (Thank you very much= Merci Beaucoup) 🙂
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;
Thanks. I agree that this is more readable (esp. for less experienced SAS users), but my intention was to keep the code short. A compromise might be to use "H:MM" literals like '0:10't.
Looking at my code answers your question. ~eof means not eof.
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;
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.