topic Average of every n rows in SAS Programming

Average of every n rows

pvm90 — Thu, 13 Sep 2018 00:10:09 GMT

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!

Re: Average of every n rows

novinosrin — Thu, 13 Sep 2018 00:19:58 GMT


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;

Re: Average of every n rows

pvm90 — Thu, 13 Sep 2018 00:23:37 GMT

Thank you so much! That is perfect.

Re: Average of every n rows

Reeza — Thu, 13 Sep 2018 00:59:41 GMT

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;

Re: Average of every n rows

ballardw — Thu, 13 Sep 2018 17:57:25 GMT

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.