5 minute average for data with multiple entries per minute

[annie_1]

Hi, 

I have a days worth of data that is time marked almost every second, where every second a concentration of X chemical is recorded.

I have been able to round the time to the nearest minute in SAS, however I am trying to get the average of all the concentrations starting with my first recorded minutes until the 5th minute, and then so on every 5 minutes.

So in this case I want the average of '4:42, 4:43, 4:44, 4:45, and 4:46' then the next five minutes etc.

Below is a snippet of the data. The first two columns are the original date and time, X is the chemical and its recorded volume. Date time is just the combination of the first two columns, and the second to last column is the SAS_rounded time, and the last column is the Date and Sas_rounded time added together.

As you can see I have multiple values for just one minute. 

I tried using the MOVAVE function with a unit of 5, but it was only taking the average of the first five entries of X, and so on. I want the code to be able to get the average of the first 5 minutes of X recordings and so on.

I also added the code I tried to use for this.

Any help with this would be great, thanks.

Example of Data

DATE	TIME	X	Date time	sas_rounded	sas_datetime
5/26/2019	4:42:04	.1003	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:04	.025	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:05	.3623	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:06	1.5256	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:07	22.3626	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:07	2.2256	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:08	3.3458	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:09	6.3565	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:10	7.5642	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:10	8.2356	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:11	2.5655	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:12	5.235	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:13	-8.22	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:13	-.123	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:14	-5.235	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:15	5.2546	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:16	-20.256	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:16	6.3412	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:17	-5.2532	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:18	3.5687	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:19	6.43	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:19	5.2356	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:20	0.2535	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42
5/26/2019	4:42:21	-2.5254	5/26/2019 4:42	4:42:00 AM	5/26/2019 4:42

proc expand data = twentysix out = twentysix_5min method=None;
CONVERT X = X_ave/TRANSFORM=(MOVAVE 5);
by sas_datetime;
run;
proc expand data = twentysix out = twentysix_5min_TRY method=None;
CONVERT X = X_ave / TRANSOUT=(MOVAVE 5 trim 4);
by sas_datetime;
run;

[Reeza]

You definitely don't want that BY statement in there.

Try it without and then with MOVAVE but SAS marks time in seconds, so 5 minutes may be 60*5 = 300?

If not, its pretty straightforward to do via PROC MEANS or other options. Are you trying to do a moving average or an average for every 5 minute? I thought you want the average for 0-4, 5-9 not 0-4, 1-5, 2-6, which is the moving average.

[annie_1]

You are correct I want the average for 0-4 min, 5-9 min etc.

           So I should take out the transform function and use the proc means option?

Thank you for responding.

[Reeza]

You need proc means but unfortunately you'll have to either create a custom format to group your times or create a new variable that will group them into 5 minutes. Do you know how to do that or do you need help with it? If you're a beginner, creating a new variable that had the grouping is the best method. I would not use IF/THEN statements but use the fact that times are in seconds and that you can round them to the nearest value. You usually have to adjust the formula slightly, by subtracting or adding a constant to get exactly what you want for rounding.

[Reeza]

data want;
set have;

*find start of time block;
time_start = max(round(time - 150 , 300), 0);
*find end of time block;
time_end = round(time + 150, 300);
*create a text label of data;
time_group = catx(" to ", put(minute(time_start), z2.), put(minute(time_end), z2.));
*format to control appearance;
format time: time.;
run;

May need some tweaks, not fully tested.

[annie_1]

OK, great. I will try it out.

Thank you for the code, I appreciate your help.

A