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

jerrylshen — Wed, 21 Aug 2019 19:45:28 GMT

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        ║
╚═════════╩══════════╩══════════╩═══════════╩═══════════╝

╔═════════╦══════════╦══════════╦═══════════╦═══════════╗
║ 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.

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

Reeza — Wed, 21 Aug 2019 17:39:12 GMT

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;

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

jerrylshen — Wed, 21 Aug 2019 17:45:09 GMT

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;

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

PaigeMiller — Wed, 21 Aug 2019 18:00:29 GMT

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?

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

Reeza — Wed, 21 Aug 2019 18:03:40 GMT

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

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

jerrylshen — Wed, 21 Aug 2019 19:44:16 GMT

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!

topic Re: SAS/SQL: Find the median of three times, not min/max in SAS Programming

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

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

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

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

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

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