Calculate median and 95% C.I. for time variables (hr:min:sec)

[Denali]

Hi,

How do I analyze the time variable with hour:minute:second? I would like to calculate the median and 95% C.I. for column I (Culture_time), J (Treatment_time) and K (Total_time). Please see attached dataset (Sample1.xlsx).

Thanks very much in advance!

[ed_sas_member]

Hi @Denali 

You can try the below code:

- First, be sure to correctly import time values in SAS -> apply an informat to be sure that SAS will consider time values as valid one and store them as numbers. You can then apply a format for display

- To calculate the median, you can use either proc univariate or proc means for example. As the distributions are not normals (cf. requested plots), you cannot calculate a CI95%

Best,

data have;
	infile datalines dlm="09"x;
	/*Apply the informat time10. to ensure SAS consider those values as time values (stored as numeric values)*/
	input Culture_timei:time10.	Treatment_time:time10.	Total_time:time10.;
	/*Apply a format for display so that time value can be 'human-readable' and meaningful*/
	format 	Culture_timei Treatment_time Total_time time10.;
	datalines;
37:16:00	34:34:00	71:50:00
29:31:00	1:45:00	31:16:00
34:38:00	5:14:00	39:52:00
37:57:00	19:52:00	57:49:00
68:38:00	1:50:00	70:28:00
25:25:00	0:59:00	26:24:00
20:25:00	1:21:00	21:46:00
30:29:00	0:00:00	5:49:00
101:48:00	0:00:00	34:47:00
53:20:00	1:21:00	54:41:00
25:34:00	1:58:00	27:32:00
25:27:00	1:35:00	27:02:00
28:55:00	0:00:00	1:08:00
26:08:00	46:50:00	72:58:00
24:44:00	1:40:00	26:24:00
120:24:00	14:42:00	135:06:00
34:21:00	0:00:00	28:09:00
99:02:00	26:51:00	125:53:00
24:31:00	8:08:00	32:39:00
26:36:00	0:00:00	0:00:00
156:10:00	0:00:00	58:43:00
51:06:00	20:12:00	71:18:00
71:10:00	0:00:00	8:14:00
88:54:00	2:13:00	91:07:00
84:35:00	0:00:00	9:13:00
46:21:00	7:32:00	53:53:00
44:22:00	0:00:00	1:55:00
81:58:00	6:47:00	88:45:00
104:38:00	255:22:00	360:00:00
74:42:00	2:27:00	77:09:00
52:36:00	0:19:00	52:55:00
54:51:00	2:35:00	57:26:00
20:19:00	5:02:00	25:21:00
55:12:00	2:54:00	58:06:00
32:12:00	4:50:00	37:02:00
70:32:00	0:00:00	7:07:00
114:58:00	119:38:00	234:36:00
49:37:00	0:00:00	18:38:00
47:49:00	3:29:00	51:18:00
53:00:00	0:48:00	53:48:00
44:44:00	4:59:00	49:43:00
33:11:00	1:36:00	34:47:00
17:18:00	2:51:00	20:09:00
31:00:00	8:04:00	39:04:00
42:36:00	3:55:00	46:31:00
34:07:00	0:00:00	1:39:00
83:07:00	2:16:00	85:23:00
43:29:00	1:40:00	45:09:00
24:19:00	3:21:00	27:40:00
72:59:00	0:00:00	28:27:00
40:17:00	2:03:00	42:20:00
39:57:00	2:12:00	42:09:00
48:53:00	3:07:00	52:00:00
19:33:00	3:45:00	23:18:00
64:30:00	13:20:00	77:50:00
56:14:00	20:34:00	76:48:00
52:00:00	8:19:00	60:19:00
32:17:00	0:00:00	16:20:00
58:14:00	0:00:00	7:11:00
19:15:00	0:42:00	19:57:00
24:50:00	5:17:00	30:07:00
106:40:00	0:00:00	2:58:00
33:05:00	2:44:00	35:49:00
33:11:00	6:47:00	39:58:00
84:19:00	54:05:00	138:24:00
37:33:00	3:37:00	41:10:00
30:32:00	4:04:00	34:36:00
84:31:00	0:00:00	7:56:00
13:30:00	1:08:00	14:38:00
24:27:00	3:01:00	27:28:00
42:50:00	0:00:00	20:12:00
39:43:00	0:00:00	22:02:00
124:15:00	0:00:00	0:00:00
41:01:00	4:25:00	45:26:00
18:31:00	2:49:00	21:20:00
27:49:00	0:43:00	28:32:00
70:49:00	3:01:00	73:50:00
34:23:00	1:32:00	35:55:00
102:16:00	0:00:00	49:30:00
34:42:00	0:00:00	17:15:00
71:11:00	11:48:00	82:59:00
23:20:00	11:47:00	35:07:00
35:04:00	14:36:00	49:40:00
30:16:00	22:46:00	53:02:00
95:14:00	0:00:00	5:13:00
37:30:00	0:00:00	0:00:00
57:23:00	2:22:00	59:45:00
32:31:00	1:57:00	34:28:00
65:59:00	3:51:00	69:50:00
78:41:00	12:37:00	91:18:00
49:38:00	0:00:00	0:00:00
87:02:00	6:34:00	93:36:00
43:46:00	4:01:00	47:47:00
38:44:00	38:36:00	77:20:00
41:26:00	22:58:00	64:24:00
39:42:00	8:56:00	48:38:00
50:57:00	21:13:00	72:10:00
35:14:00	9:34:00	44:48:00
60:31:00	3:33:00	64:04:00
37:16:00	4:17:00	41:33:00
53:32:00	8:31:00	62:03:00
36:57:00	15:43:00	52:40:00
97:36:00	2:01:00	99:37:00
88:54:00	0:00:00	11:22:00
48:56:00	19:00:00	67:56:00
58:32:00	0:00:00	0:00:00
94:00:00	0:57:00	94:57:00
92:58:00	0:00:00	2:13:00
57:17:00	0:00:00	6:37:00
53:13:00	6:36:00	59:49:00
48:25:00	1:42:00	50:07:00
74:07:00	3:01:00	77:08:00
40:28:00	29:29:00	69:57:00
38:03:00	5:41:00	43:44:00
81:59:00	0:00:00	9:31:00
67:22:00	4:15:00	71:37:00
40:42:00	9:06:00	49:48:00
27:18:00	0:56:00	28:14:00
33:08:00	7:58:00	41:06:00
15:48:00	0:00:00	0:00:00
38:47:00	0:00:00	0:00:00
23:22:00	2:59:00	26:21:00
69:11:00	0:00:00	32:19:00
26:26:00	2:11:00	28:37:00
41:59:00	23:50:00	65:49:00
37:24:00	11:44:00	49:08:00
110:25:00	12:42:00	123:07:00
47:45:00	4:05:00	51:50:00
34:06:00	0:22:00	34:28:00
35:01:00	4:20:00	39:21:00
20:00:00	6:13:00	26:13:00
29:33:00	2:13:00	31:46:00
30:29:00	5:32:00	36:01:00
44:02:00	0:00:00	0:00:00
92:48:00	1:18:00	94:06:00
29:45:00	2:31:00	32:16:00
52:22:00	0:37:00	52:59:00
12:34:00	4:57:00	17:31:00
81:51:00	0:00:00	33:22:00
41:48:00	6:53:00	48:41:00
51:47:00	7:07:00	58:54:00
68:53:00	2:20:00	71:13:00
93:35:00	0:45:00	94:20:00
70:31:00	25:32:00	96:03:00
63:48:00	11:09:00	74:57:00
34:14:00	14:53:00	49:07:00
155:32:00	0:00:00	62:18:00
37:54:00	8:29:00	46:23:00
145:38:00	0:00:00	82:41:00
120:56:00	31:41:00	152:37:00
102:11:00	0:00:00	33:41:00
39:43:00	2:09:00	41:52:00
103:03:00	0:00:00	82:19:00
96:40:00	0:00:00	77:13:00
122:33:00	0:00:00	33:37:00
81:00:00	0:00:00	70:54:00
70:48:00	0:00:00	0:00:00
87:51:00	0:00:00	0:00:00
55:23:00	0:00:00	21:25:00
60:08:00	0:00:00	39:02:00
59:27:00	0:00:00	0:07:00
;
run;

/* Compute summary statistics: median values */

proc univariate data=have plots;
	var Culture_timei Treatment_time Total_time;
	output out=want median=Culture_timei Treatment_time Total_time;
run;

/* Output median values in a dataset with the time format */

data want;
	set want;
	format 	Culture_timei Treatment_time Total_time time10.;
run;

[Denali]

@ed_sas_member Thank you so much for sharing the code! It worked, but I still need to generate 95% C.I. and export to the "want" dataset. Can we add another code or syntax somewhere?  Thanks again!

[Ksharp]

Quantile Regression and proc univariate can do that.

ods select Quantiles;
proc univariate data=Heights ciquantdf(alpha=.1);
var Height;
run

calling @Rick_SAS 

[Rick_SAS]

As KSharp says, you can use the CIPCTLDF option to compute a 95% CI for a percentile. (KSharp's example uses alpha=0.1, which will give 90% CIs.) By default, you will get CIs for the following percentiles: 1, 5, 10, 25, 50, 75, 90, 95, 99 

If you need other percentiles, you can follow the examples in this blog post: "Compute confidence intervals for percentiles in SAS."

As KSharp mentions, you can also estimate for quantiles and CIs by using PROC QUANTREG. 

[Denali]

Thank you both for your detailed explainations. I ran proc contents and saw Total_time was in Time8. format. Then I ran the proc univariate with 95% C.I. below.

ods select Quantiles;
proc univariate data=Sample1 ciquantdf(alpha=.05);
var Total_time;
run;

But I don't understand what the units are in the below SAS Output. Are these numbers in second?

The UNIVARIATE Procedure

Variable: TOTAL_ Time

Quantiles (Definition 5)
Level	Quantile			Order Statistics
		95% Confidence Limits		LCL Rank	UCL Rank	Coverage
		Distribution Free
100% Max	1296000
99%	844560	486360	1296000	157	161	77.82
95%	345780	328680	549420	148	159	95.64
90%	307380	277740	345780	138	153	95.26
75% Q3	231840	208140	262680	111	133	95.28
50% Median	150720	128940	176880	69	94	95.08
25% Q1	94380	62100	102420	29	51	95.28
10%	18780	0	33180	9	24	95.26
5%	0	0	6900	3	14	95.64
1%	0	0	0	1	5	77.82
0% Min	0

Thanks again!

[Rick_SAS]

Yes. See the doc for the TIME. informat. When you use the TIME. informat to read the data, the "value is the total number of seconds in the time value."

[Denali]

How do I export the 95% C.I. and convert them into the hr:min:sec format?

[Rick_SAS · Accepted solution]

You can use the ODS OUTPUT statement to create a SAS data set from any table. Then use the FORMAT statement in PROC PRINT:

proc univariate data=have cipctldf;
	var Culture_timei Treatment_time Total_time;
	ods output Quantiles=Q;  /* create SAS data set called Q */
run;

proc print data=Q;
format Estimate LCLDistFree UCLDistFree TIME.;
by notsorted VarName;
var Quantile Estimate LCLDistFree UCLDistFree;
run;

[Denali]

@Rick_SAS  I just want to make sure that this code does generate 95% C.I., not 90% CI, right? Thank you so much!

[Rick_SAS]

Correct. alpha=0.05 is the default so

CIPCTLDF;   /* default is alpha=0.05 */

is equivalent to

CIPCTLDF(alpha=0.05);