topic Re: Calculate median and 95% C.I. for time variables (hr:min:sec) in SAS Programming

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

Denali — Thu, 11 Jun 2020 03:33:32 GMT

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!

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

ed_sas_member — Thu, 11 Jun 2020 08:06:07 GMT

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;

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

Ksharp — Thu, 11 Jun 2020 11:25:18 GMT

Quantile Regression and proc univariate can do that.

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

calling @Rick_SAS

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

Rick_SAS — Thu, 11 Jun 2020 12:34:39 GMT

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.

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

Denali — Thu, 11 Jun 2020 13:10:35 GMT

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!

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

Rick_SAS — Thu, 11 Jun 2020 13:16:08 GMT

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."

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

Denali — Thu, 11 Jun 2020 14:23:17 GMT

@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!

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

Denali — Thu, 11 Jun 2020 14:59:57 GMT

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

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

Rick_SAS — Thu, 11 Jun 2020 15:17:37 GMT

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;

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

Denali — Thu, 11 Jun 2020 17:14:09 GMT

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

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

Rick_SAS — Thu, 11 Jun 2020 17:41:13 GMT

Correct. alpha=0.05 is the default so

CIPCTLDF; /* default is alpha=0.05 */

is equivalent to

CIPCTLDF(alpha=0.05);