Hi,
I have a data set that has a measure of the temperature every 10 minutes (variable = temp) for a month. I would like to get an average of every 6 rows combined so I can calculate the average temperature per hour.
An example of the first bit of my data is:
data WORK.WEATHER;
infile datalines truncover;
input date: DDMMYY10. time:TIME. temp:32.;
format date DDMMYY10. time TIME.;
datalines;
04/08/2018 7:00:00 18.461
04/08/2018 7:10:00 18.557
04/08/2018 7:20:00 18.675
04/08/2018 7:30:00 18.557
04/08/2018 7:40:00 18.461
04/08/2018 7:50:00 18.319
04/08/2018 8:00:00 18.39
04/08/2018 8:10:00 18.652
04/08/2018 8:20:00 19.246
04/08/2018 8:30:00 19.46
04/08/2018 8:40:00 19.793
04/08/2018 8:50:00 20.293
;
data w;
set weather;
if minute(time)=0 then grp+1;
run;
proc sql;
create table want(drop=grp) as
select date, time, mean(temp) as avg_temp
from w
group by date,grp
having time=min(time);
quit;
data WORK.WEATHER;
infile datalines truncover;
input date: DDMMYY10. time:TIME. temp:32.;
format date DDMMYY10. time TIME.;
datalines;
04/08/2018 7:00:00 18.461
04/08/2018 7:10:00 18.557
04/08/2018 7:20:00 18.675
04/08/2018 7:30:00 18.557
04/08/2018 7:40:00 18.461
04/08/2018 7:50:00 18.319
04/08/2018 8:00:00 18.39
04/08/2018 8:10:00 18.652
04/08/2018 8:20:00 19.246
04/08/2018 8:30:00 19.46
04/08/2018 8:40:00 19.793
04/08/2018 8:50:00 20.293
;
data w;
set weather;
if minute(time)=0 then grp+1;
run;
proc sql;
create table want(drop=grp) as
select date, time, mean(temp) as avg_temp
from w
group by date,grp
having time=min(time);
quit;
Thank you so much! That is perfect.
I'd suggest a modification. Use the HOUR() function on the time variable and then you can have the hour as well with your summaries.
data temp;
set have;
hour = hour(time);
run;
Another approach using a DATETIME value and formats:
data WORK.WEATHER; infile datalines truncover; input date: DDMMYY10. time:TIME. temp:32.; format date DDMMYY10. time TIME.; dt = dhms(date,0,0,time); format dt datetime18.; datalines; 04/08/2018 7:00:00 18.461 04/08/2018 7:10:00 18.557 04/08/2018 7:20:00 18.675 04/08/2018 7:30:00 18.557 04/08/2018 7:40:00 18.461 04/08/2018 7:50:00 18.319 04/08/2018 8:00:00 18.39 04/08/2018 8:10:00 18.652 04/08/2018 8:20:00 19.246 04/08/2018 8:30:00 19.46 04/08/2018 8:40:00 19.793 04/08/2018 8:50:00 20.293 ; proc summary data=work.weather nway; class dt; format dt datetime10.; var temp; output out=work.weathermean (drop=_:) mean=; run;
The output data set will have the first datetime value that rounds to an hour by using the datetime10 format. Change the format in other displays if you want to see it appear differently.
Or no added variable just a different format for the Time variable:
proc summary data=work.weather nway; class date time; format time time2.; var temp; output out=work.weathermean2 (drop=_:) mean=; run;
Again, you can change the time variable format in other displays to get back to an hh:mm:ss appearance.
Either of these will also work if you have more or fewer records within the hour.
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 16. 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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: