How to handle time (mm:ss.ss) values in SAS

8

One of my earlier blogs on handling percent (%) values in SAS was very popular (it's been viewed over 34,000 times!), so I thought I'd write a similar blog on handling time (mm:ss.ss) values in SAS ...

This past weekend I was in a dragonboat race (that's me in the red hat)...

dragonboat_race

After the race, I decided to create some graphs with all the times from the race day, to see how our team compared to all the other teams. But I found that some of the average times I was calculating with SAS didn't always match the average times listed on the race website. I double- and triple-checked my calculations (even calculating a few by hand), and finally contacted the people in charge of the race. They sent me a copy of the spreadsheet they had used to calculate the averages, and I found that they were treating the digits of the time values as decimal digits rather than actual time values (the tricky thing is that most of the averages looked correct ... but that was just by luck!)

I thought this an interesting enough problem that I should create an example, to hopefully steer data analysts in the right direction when working with time data ... and avoid the data-integrity problems that result in (mis)handling time digits as decimal numbers.

For this demonstration, I have 4 teams, each running 3 races/heats. If a race time is 1 minute 21.85 seconds, then you might be tempted to (incorrectly) enter the data into a spreadsheet as the decimal digits 0121.85. Here's a table of four teams' data, stored in this (incorrect) way:

race_times_decimal

And if I plot these decimal values, and their average (in red), I get the following graph. It looks pretty nice, and the averages seem reasonable ... at first glance.

time_data

But upon closer examination, it seems odd that one of Team 4's races is so much slower than their other two races. And if you look at Team 4's average - what is 1:71.70 (how can you have a '71' in the minutes place?!?) And if you look at the horizontal/time axis, you'll notice that it shows a range for '1:60' to '2:00' ... which are impossible times.

time_data1

 

Now that you've seen the problem with handling the time values the wrong way, let's do it the right way! First, I input the time values into a SAS dataset, as proper time values (behind the scenes, SAS is storing the values as the number of seconds, and you can use any of SAS' time formats to have the values print out in whatever time format you want, such as mm:ss.ss):

data my_data_time;
informat race1 race2 race3 stimer8.;
input team $ 1-6 race1 race2 race3;
average=mean(race1, race2, race3);
datalines;
Team 1 01:21.85 01:23.57 01:27.63
Team 2 01:28.31 01:29.66 01:29.88
Team 3 01:40.06 01:39.25 01:45.16
Team 4 01:55.72 01:53.38 02:06.00
;
run;

And now the table of data looks like this (notice the average time for Team 4 is 1:58.37, rather than 1:71:70).

race_times_time

And when I plot the data, there is no bogus range of times, and Team 4's values are much closer together. (And, most importantly, all the values are correct!)

time_data2

 

Here is a link to the full SAS code, if you'd like to download it and experiment with it.

(Note: I've updated this example to use the stimer8. informat, rather than the time11.2 informat, based on KSharp's suggestion - either of the informats would get the job done, but stimer seems a bit more convenient to use in this particular situation.)

Share

About Author

Robert Allison

The Graph Guy!

Robert has worked at SAS for over 25 years, and is perhaps the foremost expert in creating custom graphs using SAS/GRAPH. His educational background is in Computer Science, and he holds a BS, MS, and PhD from NC State University. He is the author of several conference papers, has won a few graphic competitions, and has written a book (SAS/GRAPH: Beyond the Basics).

8 Comments

      • Rob,
        I can't believe there is a DragonBoat match in U.S. . That should belong to China. Surprise!

        In SAS , there is a special informat for such scenario -- stimer11.

        And more important thing is you don't need to pad 0 in front of it to make hours.
        In other words, stimer11. can handle such data :

        Team 1 01:21.85 01:23.57 01:27.63
        Team 2 01:28.31 01:29.66 01:29.88
        Team 3 01:40.06 01:39.25 01:45.16
        Team 4 01:55.72 01:53.38 02:06.00

  1. Anytime I bring an Oracle date into SAS I always have to divide it by the number of seconds in a day (86,400) for SAS to treat it like any other SAS date value.

    • Robert Allison
      Robert Allison on

      Hmm ... I guess that indicates you're converting a time value (number of seconds) to a date value (number of days)?

  2. Yes, SAS date value: is a value that represents the number of days between January 1, 1960, and a specified date.

Leave A Reply

Back to Top