topic Re: Calculate a metric from time series data in SAS Programming

Calculate a metric from time series data

serenedion — Fri, 15 Apr 2022 19:23:01 GMT

Hi Everybody!

I have a question on some data. I am measuring potential (V) at minute intervals, and I want to know the duration (and percentage) of time that this potential is within a certain range.

For example, if the data looked like this:

Time (min)	V (uV)
1	0.229528
2	0.262015
3	0.06782
4	1.263749
5	2.969982
6	2.831766
7	1.749711
8	0.164038
9	0.685758
10	0.924174
11	0.150946
12	0.384327
13	1.135237
14	1.9148
15	1.039687

How would I calculate the amount of time that the variable V is:

1) < 1 uV

2) in the range 1-2 uV (including 1 and 2 uV)

3) > 2 uV

The sum of these three times should also add to make 15 minutes as well.

I have tried some basic code but I would really appreciate peoples help!

Kind regards,
Dion

Re: Calculate a metric from time series data

ballardw — Fri, 15 Apr 2022 19:44:49 GMT

A very powerful tool in SAS is the custom format. You can create ranges that will be used by Reporting or Analysis procedures to make groups. Most of the graphing procedures will honor them as well.

Here is an example of creating a data set with data step code (which the preferred way to show data as we can test code against such a data set when we run it). Then create a custom format and demonstrate how Proc Freq will generate the counts, running total, percent of values and running percent.

data have;
   input minutes Uv;
datalines;
1	0.229528
2	0.262015
3	0.06782
4	1.263749
5	2.969982
6	2.831766
7	1.749711
8	0.164038
9	0.685758
10	0.924174
11	0.150946
12	0.384327
13	1.135237
14	1.9148
15	1.039687
;

proc format;
value uvrange
low -<1 = 'uV<1'
1 - 2   = 'uV 1 to 2'
2<-high = 'uV>2'
;
proc freq data=have;
  tables uv;
  format uv uvrange.;
run;
data want;
   set have;
   Low = (uv < 1);
   Mid = (1 le uv le 2)l
   high

One of the very powerful advantages of a format is that when the boss asks "what about interval boundaries at 1.5 and 2.5?" or anything similar. The only requirement would the create a new format and use that with the Prod Freq code instead of this Uvrange format.

Note that this works for just about anything where groups ONLY depend on values of a single variable.

If your data had another variable, such as the machine or operator that made the recording you could include that variable on the tables statement to get the counts and percentages for each machine or operator (or test sequence or other identifier like this:

proc freq data=have;
  tables uv * operatorid;
  format uv uvrange.;
run;

Re: Calculate a metric from time series data

serenedion — Fri, 15 Apr 2022 20:04:38 GMT

Thank you for your quick reply ballardw! It is really useful. I see know how you have applied a format to set the variable as low high or medium, and then run a proc freq of this new formatted variable.

Can I just check, what does the last bit of code do? Specifically:

data want;
   set have;
   Low = (uv < 1);
   Mid = (1 le uv le 2)l
   high

Does this code calculate the length of time that uV is in each range (rather than the number of points in the variable uV that are Low, Mid or High)? For this, could I add minutes to proc freq?

Re: Calculate a metric from time series data

ballardw — Fri, 15 Apr 2022 20:23:04 GMT

@serenedion wrote:

Thank you for your quick reply ballardw! It is really useful. I see know how you have applied a format to set the variable as low high or medium, and then run a proc freq of this new formatted variable.

Can I just check, what does the last bit of code do? Specifically:
data want;
   set have;
   Low = (uv < 1);
   Mid = (1 le uv le 2)l
   high
Does this code calculate the length of time that uV is in each range (rather than the number of points in the variable uV that are Low, Mid or High)? For this, could I add minutes to proc freq?

typo. Started to create new variables. The code would have created 3 variables that were 1/0 coded. Just forgot to remove it.

Re: Calculate a metric from time series data

HB — Fri, 15 Apr 2022 21:10:15 GMT

@serenedion wrote:

Does this code calculate the length of time that uV is in each range (rather than the number of points in the variable uV that are Low, Mid or High)? For this, could I add minutes to proc freq?

You indicated each point was a minute. So 5 points is 5 minutes.

Are you also wanting "streaks"? Your data would have "streaks" of 3,1,2,1,5, and 3 presumeably?

Re: Calculate a metric from time series data

serenedion — Sat, 16 Apr 2022 15:25:00 GMT

Hi HB, thanks for getting back to me. It took a while to work out what you meant!! But yes, I guess that could be a useful metric to report aswell. How would you identify and calculate these different streaks? Could you assign to each streak a duration? The other issue I have is that some of my data points are missing, so how would I handle this?