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;
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.
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.
OK, great. I will try it out.
Thank you for the code, I appreciate your help.
A
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!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.