SAS/SQL: Find the median of three times, not min/max

[jerrylshen]

Follow-Up/Related to my previous question: https://communities.sas.com/t5/SAS-Programming/SAS-SQL-3-times-check-if-there-s-an-interval-greater-...   

If I have 3 times, how can I sort it so that it goes from least to greatest or greatest to least?

Input: 

╔═════════╦══════════╦══════════╦═══════════╦═══════════╗
║ Subject ║ TimeONE  ║ TimeTWO  ║ TimeTHREE ║ TimePoint ║
╠═════════╬══════════╬══════════╬═══════════╬═══════════╣
║       1 ║ 11:13:10 ║ 11:17:10 ║ 11:15:10  ║ s1        ║
║       1 ║          ║ 12:00:01 ║ 9:05:00   ║ s2        ║
╚═════════╩══════════╩══════════╩═══════════╩═══════════╝

Output:

╔═════════╦══════════╦══════════╦═══════════╦═══════════╗
║ Subject ║ TimeONE  ║ TimeTWO  ║ TimeTHREE ║ TimePoint ║
╠═════════╬══════════╬══════════╬═══════════╬═══════════╣
║       1 ║ 11:13:10 ║ 11:15:10 ║ 11:17:10  ║ s1        ║
║       1 ║          ║ 9:05:00  ║ 12:00:01  ║ s2        ║
╚═════════╩══════════╩══════════╩═══════════╩═══════════╝

or

╔═════════╦══════════╦══════════╦═══════════╦═══════════╗
║ Subject ║ TimeONE  ║ TimeTWO  ║ TimeTHREE ║ TimePoint ║
╠═════════╬══════════╬══════════╬═══════════╬═══════════╣
║       1 ║ 11:13:10 ║ 11:15:10 ║ 11:17:10  ║ s1        ║
║       1 ║ 9:05:00  ║ 12:00:01 ║           ║ s2        ║
╚═════════╩══════════╩══════════╩═══════════╩═══════════╝

My Current code based on this data table:

╔═════════╦══════════╦════════════╗
║ Subject ║  Time1   ║  TimePoint ║
╠═════════╬══════════╬════════════╣
║       1 ║ 11:13:10 ║  s1        ║
║       1 ║ 11:15:10 ║  s1        ║
║       1 ║ 11:17:30 ║  s1        ║
║       1 ║ 11:45    ║  s2        ║
║       1 ║          ║  s2        ║
║       1 ║ 11:50    ║  s2        ║
╚═════════╩══════════╩════════════╝ 

proc sql;
CREATE TABLE t1 AS
SELECT DISTINCT *
FROM have
GROUP BY Subject, timepoint
HAVING timepoint IS NOT NULL
ORDER BY  TimeOne, TimeTWO, TimeThree
;

CREATE TABLE t4 AS
SELECT t1.Subject, t1.timepoint, 
t1.EG1TIM AS T1, t2.EG1TIM AS T2, t3.EG1TIM AS T3, 0 AS T3_MINUS_T2, 0 AS T2_MINUS_T1, 0 AS T3_MINUS_T1
FROM t1, t1 t2, t1 t3
WHERE /*make sure same subject and same timepoint */
order by t1.eg1tim 
;

/*This essentially becomes the input/output table shown at the very top of this post, but not sorted */

quit;

I'm just stuck on sorting the 3 times. I need to determine, given it's sorted, if there's an interval that's > 2min (120seconds)

Thanks

EDIT after accepting solution:   

I used sortc(Time1, Time2, Time3) and then just manually find the difference of each interval, taking into account if a Time is null in a data step.

[Reeza]

If you use a data step you can use CALL SORT()

If you're using SQL, you'll have to use CASE statements to explicitly create the order. SQL works with columns at a time, not rows.

Did my previous solution not work? If so, how did it not work?


This will sort your times.
data want;
set have;
call sort (time1, time2, time3);

run;

[jerrylshen]

Yeah it just didn't work and changing from long to wide format still leaves me to tackle the sorting/finding difference issue. I'll try your first solution again.   

I also got this error when running your current solution:

17061 data t5;
17062 set t4;
17063 call sort (T1, T2, T3);
----
251
ERROR 251-185: The subroutine SORT is unknown, or cannot be accessed. Check your spelling.
Either it was not found in the path(s) of executable images, or there was incorrect
or missing subroutine descriptor information.

17064
17065 run;

[PaigeMiller]

CALL SORT only runs on the CAS server, which obviously is not where you are trying to run this.

Does not the MEDIAN() function work in this situation?

[Reeza · Accepted solution]

Sorry, its CALL SORTN() and CALL SORTC() for numeric and character.

[jerrylshen]

Yup, thanks!   

I used sortc(Time1, Time2, Time3) and then just manually find the difference of each interval, taking into account if a Time is null in a data step.

Thanks!