Average of every n rows

[pvm90]

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 dsd 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

I would like it to look like this:

04/08/2018 7:00:00 18.505

04/08/2018 8:00:00 19.306 

Thank you!

[novinosrin · Accepted solution]

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;

[pvm90]

Thank you so much! That is perfect.

[Reeza]

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;

[ballardw]

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.