Change format of elapsed time from seconds to mm:ss

[Ben Hando]

I have 1.5 mile run times recorded in seconds. I would like to keep this variable as seconds for the analyses I am running (i.e. regression). But I would like to display the format as mm:ss for the output in graphs and tables. I.e. the way you can display the date in any format, but it leaves the numbers the same (days from 1/1/1960).

Data is below: 3 variables: First_Run is the run time in seconds. first_run2 is just First_Run / 60. first_run 3 I generated with: .egen first_run3 = hmmss(First_run). first_run2 doesn't help as it's in the wrong format. first_run3 displays correctly, but it's a string and therefore unusable for analyses.

Appreciate any help/insight and I apologize if this is too basic for this forum!
Ben

----------------------- copy starting from the next line -----------------------

* Example generated by -dataex-. To install: ssc install dataex
clear
input long First_Run float first_run2 str8 first_run3
571  9.516666 "09:31"
632 10.533334 "10:32"
571  9.516666 "09:31"
607 10.116667 "10:07"
539  8.983334 "08:59"
579      9.65 "09:39"
579      9.65 "09:39"
595  9.916667 "09:55"
550  9.166667 "09:10"
576       9.6 "09:36"
620 10.333333 "10:20"
564       9.4 "09:24"
654      10.9 "10:54"
562  9.366667 "09:22"
599  9.983334 "09:59"
694 11.566667 "11:34"
587  9.783334 "09:47"
666      11.1 "11:06"
646 10.766666 "10:46"
625 10.416667 "10:25"
  0         0 "00:00"
618      10.3 "10:18"
621     10.35 "10:21"
651     10.85 "10:51"
642      10.7 "10:42"
549      9.15 "09:09"
684      11.4 "11:24"
602 10.033334 "10:02"
573      9.55 "09:33"
640 10.666667 "10:40"
559  9.316667 "09:19"
635 10.583333 "10:35"
677 11.283334 "11:17"
  .         . ""     
745 12.416667 "12:25"
657     10.95 "10:57"
648      10.8 "10:48"
  .         . ""     
694 11.566667 "11:34"
648      10.8 "10:48"
end

[Clyde Schechter]

gen double wanted = First_Run*1000
format wanted %tcmm:ss

[Ben Hando]

A ha...okay, so you first convert to milliseconds? Makes sense. Thanks so much, Clyde!

Ben

[Nick Cox]

I get that 1.5 miles in about 10 seconds is about 0.15 miles in 1 second or about 0.15 * 60 * 60 or 540 miles per hour. Whose superpower is this?

[Nick Cox]

More seriously, if these times are really minutes, then

gen wanted = string(floor(first_run2), "%02.0f") + ":" + string(floor(60 * mod(first_run2, 1)), "%02.0f")

gets you closer to what you ask, but there are several discrepancies. Note that times of zero are implausible except as missing.

[Ben Hando]

HI Nick - thanks for your help here. But I don't totally follow. I've pasted the data below. First_Run = original format of just seconds. First_Run2 is Clyde's solution that I thought worked. And First_Run4 is your solution. It looks like yours and Clyde's match up, but now First_Run4 is a string and therefore unusable. But I'm not seeing the problem with Clyde's solution.

* Example generated by -dataex-. To install: ssc install dataex
clear
input long First_Run double First_Run2 str6 First_Run4
571 571000 "09:30"
632 632000 "10:32"
571 571000 "09:30"
607 607000 "10:07"
539 539000 "08:59"
579 579000 "09:38"
579 579000 "09:38"
595 595000 "09:55"
550 550000 "09:10"
576 576000 "09:36"
620 620000 "10:19"
564 564000 "09:23"
654 654000 "10:53"
562 562000 "09:22"
599 599000 "09:59"
694 694000 "11:33"
587 587000 "09:47"
666 666000 "11:06"
646 646000 "10:45"
625 625000 "10:25"
  0      0 "00:00"
618 618000 "10:18"
621 621000 "10:21"
651 651000 "10:51"
642 642000 "10:41"
549 549000 "09:08"
684 684000 "11:23"
602 602000 "10:02"
573 573000 "09:33"
640 640000 "10:40"
559 559000 "09:18"
635 635000 "10:34"
677 677000 "11:17"
  .      . ".:."  
745 745000 "12:25"
657 657000 "10:56"
648 648000 "10:48"
  .      . ".:."  
694 694000 "11:33"
648 648000 "10:48"
758 758000 "12:37"
803 803000 "13:22"
582 582000 "09:41"
648 648000 "10:48"
659 659000 "10:59"
614 614000 "10:14"
567 567000 "09:26"
774 774000 "12:53"
670 670000 "11:10"
669 669000 "11:08"
663 663000 "11:03"
623 623000 "10:22"
  .      . ".:."  
607 607000 "10:07"
577 577000 "09:37"
550 550000 "09:10"
653 653000 "10:52"
658 658000 "10:57"
  .      . ".:."  
  .      . ".:."  
884 884000 "14:44"
762 762000 "12:41"
576 576000 "09:36"
  0      0 "00:00"
708 708000 "11:48"
636 636000 "10:36"
  0      0 "00:00"
  0      0 "00:00"
632 632000 "10:32"
769 769000 "12:48"
690 690000 "11:30"
752 752000 "12:32"
752 752000 "12:32"
624 624000 "10:23"
750 750000 "12:30"
654 654000 "10:53"
731 731000 "12:11"
542 542000 "09:02"
567 567000 "09:26"
689 689000 "11:29"
722 722000 "12:02"
593 593000 "09:52"
598 598000 "09:57"
565 565000 "09:25"
632 632000 "10:32"
606 606000 "10:06"
643 643000 "10:42"
530 530000 "08:49"
  .      . ".:."  
689 689000 "11:29"
  .      . ".:."  
609 609000 "10:08"
574 574000 "09:33"
  .      . ".:."  
640 640000 "10:40"
  0      0 "00:00"
614 614000 "10:14"
629 629000 "10:29"
689 689000 "11:29"
674 674000 "11:14"
end
format %tcMM:SS First_Run2

[Nick Cox]

Very sorry. My analysis missed (and messed up) what you made perfectly clear. Let me try to catch up a little. You can use the strings in tables, as they will sort in the right order. For graphs one approach is just to define value labels and then use the value labels on whatever axis you want.

Sample code:

forval t = 540(30)900 {
     local T = string(floor(`t'/60), "%02.0f") + ":" + string(mod(`t', 60), "%02.0f")
     label def wanted `t' "`T'" , add 
}

label li wanted


wanted:
         540 09:00
         570 09:30
         600 10:00
         630 10:30
         660 11:00
         690 11:30
         720 12:00
         750 12:30
         780 13:00
         810 13:30
         840 14:00
         870 14:30
         900 15:00

[Ben Hando]

Thanks so much, Nick. This is actually getting at a problem I'm having. My output to tables isn't maintaining the format that I used from Clyde. I.e. it's only listing the milliseconds. Using your code here should solve the problem, but I'd need to create a label for each 1 second interval vs. the 60 second interval you have here. Is that possible? Thanks again!

[Nick Cox]

That problem has, as I understand it, already been solved without recourse to value labels. Starting from the data in #7 I get this


[CODE]

. gen wanted = string(floor(First_Run/60), "%02.0f") + ":" + string(mod(First_Run, 60), "%02.0f")

. tab wanted

.:. | 8 8.00 8.00
00:00 | 5 5.00 13.00
08:50 | 1 1.00 14.00
08:59 | 1 1.00 15.00
09:02 | 1 1.00 16.00
09:09 | 1 1.00 17.00
09:10 | 2 2.00 19.00
09:19 | 1 1.00 20.00
09:22 | 1 1.00 21.00
09:24 | 1 1.00 22.00
09:25 | 1 1.00 23.00
09:27 | 2 2.00 25.00
09:31 | 2 2.00 27.00
09:33 | 1 1.00 28.00
09:34 | 1 1.00 29.00
09:36 | 2 2.00 31.00
09:37 | 1 1.00 32.00
09:39 | 2 2.00 34.00
09:42 | 1 1.00 35.00
09:47 | 1 1.00 36.00
09:53 | 1 1.00 37.00
09:55 | 1 1.00 38.00
09:58 | 1 1.00 39.00
09:59 | 1 1.00 40.00
10:02 | 1 1.00 41.00
10:06 | 1 1.00 42.00
10:07 | 2 2.00 44.00
10:09 | 1 1.00 45.00
10:14 | 2 2.00 47.00
10:18 | 1 1.00 48.00
10:20 | 1 1.00 49.00
10:21 | 1 1.00 50.00
10:23 | 1 1.00 51.00
10:24 | 1 1.00 52.00
10:25 | 1 1.00 53.00
10:29 | 1 1.00 54.00
10:32 | 3 3.00 57.00
10:35 | 1 1.00 58.00
10:36 | 1 1.00 59.00
10:40 | 2 2.00 61.00
10:42 | 1 1.00 62.00
10:43 | 1 1.00 63.00
10:46 | 1 1.00 64.00
10:48 | 3 3.00 67.00
10:51 | 1 1.00 68.00
10:53 | 1 1.00 69.00
10:54 | 2 2.00 71.00
10:57 | 1 1.00 72.00
10:58 | 1 1.00 73.00
10:59 | 1 1.00 74.00
11:03 | 1 1.00 75.00
11:06 | 1 1.00 76.00
11:09 | 1 1.00 77.00
11:10 | 1 1.00 78.00
11:14 | 1 1.00 79.00
11:17 | 1 1.00 80.00
11:24 | 1 1.00 81.00
11:29 | 3 3.00 84.00
11:30 | 1 1.00 85.00
11:34 | 2 2.00 87.00
11:48 | 1 1.00 88.00
12:02 | 1 1.00 89.00
12:11 | 1 1.00 90.00
12:25 | 1 1.00 91.00
12:30 | 1 1.00 92.00
12:32 | 2 2.00 94.00
12:38 | 1 1.00 95.00
12:42 | 1 1.00 96.00
12:49 | 1 1.00 97.00
12:54 | 1 1.00 98.00
13:23 | 1 1.00 99.00
14:44 | 1 1.00 100.00
------------+-----------------------------------
Total | 100 100.00
[/CODE[

I also note that your First_Run4 is often off by 1 second: groups is from the Stata Journal.
.

 
. groups First_Run First_Run4 wanted if wanted != First_Run4

  +------------------------------------------------+
  | First_~n   First_~4   wanted   Freq.   Percent |
  |------------------------------------------------|
  |      530      08:49    08:50       1      2.78 |
  |      549      09:08    09:09       1      2.78 |
  |      559      09:18    09:19       1      2.78 |
  |      564      09:23    09:24       1      2.78 |
  |      567      09:26    09:27       2      5.56 |
  |------------------------------------------------|
  |      571      09:30    09:31       2      5.56 |
  |      574      09:33    09:34       1      2.78 |
  |      579      09:38    09:39       2      5.56 |
  |      582      09:41    09:42       1      2.78 |
  |      593      09:52    09:53       1      2.78 |
  |------------------------------------------------|
  |      598      09:57    09:58       1      2.78 |
  |      609      10:08    10:09       1      2.78 |
  |      620      10:19    10:20       1      2.78 |
  |      623      10:22    10:23       1      2.78 |
  |      624      10:23    10:24       1      2.78 |
  |------------------------------------------------|
  |      635      10:34    10:35       1      2.78 |
  |      642      10:41    10:42       1      2.78 |
  |      643      10:42    10:43       1      2.78 |
  |      646      10:45    10:46       1      2.78 |
  |      653      10:52    10:53       1      2.78 |
  |------------------------------------------------|
  |      654      10:53    10:54       2      5.56 |
  |      657      10:56    10:57       1      2.78 |
  |      658      10:57    10:58       1      2.78 |
  |      669      11:08    11:09       1      2.78 |
  |      684      11:23    11:24       1      2.78 |
  |------------------------------------------------|
  |      694      11:33    11:34       2      5.56 |
  |      758      12:37    12:38       1      2.78 |
  |      762      12:41    12:42       1      2.78 |
  |      769      12:48    12:49       1      2.78 |
  |      774      12:53    12:54       1      2.78 |
  |------------------------------------------------|
  |      803      13:22    13:23       1      2.78 |
  +------------------------------------------------+

.

[Ben Hando]

Thanks, Nick. I think I've already taken up too much of your time. My problem remains unresolved b/c what I need to do is run basic computations on run times and display the results in mm:ss. I.e. ttests, regression, etc...So I need the values as floats to compare run times between groups. I'd just like to be able to display these results as mm:ss (i.e in a Table 1, showing differences between groups). I can only get these results to display as seconds or milliseconds (From Clyde's solution). I can't run a t-test/regression on your solution b/c it's a string. I thought perhaps applying the value labels you provided would solve this, but it doesn't seem to have worked.

I'll keep fiddling with this and I'm sure I'll get it eventually. Your ideas have helped a lot.

The data below for what it's worth: First_Run - original run times in seconds with your value label applied from #7. First_Run2 - Clyde's milisecond solution with formatting (values still show up as milliseconds in tables) and First_Run3, your string solution.

Ben

* Example generated by -dataex-. To install: ssc install dataex
clear
input long First_Run double First_Run2 str6 First_Run3
 663  663000 "11:03"
 675  675000 "11:15"
 813  813000 "13:33"
 615  615000 "10:15"
 650  650000 "10:50"
 701  701000 "11:41"
 625  625000 "10:25"
 512  512000 "08:32"
 519  519000 "08:39"
 656  656000 "10:56"
 654  654000 "10:54"
 599  599000 "09:59"
 595  595000 "09:55"
 752  752000 "12:32"
 619  619000 "10:19"
 573  573000 "09:33"
 625  625000 "10:25"
 617  617000 "10:17"
 691  691000 "11:31"
 576  576000 "09:36"
   .       . ".:."  
 585  585000 "09:45"
 504  504000 "08:24"
 714  714000 "11:54"
 650  650000 "10:50"
   .       . ".:."  
 628  628000 "10:28"
 606  606000 "10:06"
 665  665000 "11:05"
 620  620000 "10:20"
 614  614000 "10:14"
   .       . ".:."  
 630  630000 "10:30"
 631  631000 "10:31"
 657  657000 "10:57"
 580  580000 "09:40"
 617  617000 "10:17"
 632  632000 "10:32"
 687  687000 "11:27"
 585  585000 "09:45"
 644  644000 "10:44"
 662  662000 "11:02"
 551  551000 "09:11"
 613  613000 "10:13"
 640  640000 "10:40"
 556  556000 "09:16"
 600  600000 "10:00"
 733  733000 "12:13"
 541  541000 "09:01"
 648  648000 "10:48"
 757  757000 "12:37"
 585  585000 "09:45"
 946  946000 "15:46"
 581  581000 "09:41"
 624  624000 "10:24"
 595  595000 "09:55"
 633  633000 "10:33"
 603  603000 "10:03"
 723  723000 "12:03"
 740  740000 "12:20"
 893  893000 "14:53"
 570  570000 "09:30"
 910  910000 "15:10"
 584  584000 "09:44"
 705  705000 "11:45"
 632  632000 "10:32"
 631  631000 "10:31"
 667  667000 "11:07"
 574  574000 "09:34"
 559  559000 "09:19"
 632  632000 "10:32"
 724  724000 "12:04"
 626  626000 "10:26"
 664  664000 "11:04"
 657  657000 "10:57"
 598  598000 "09:58"
 586  586000 "09:46"
 477  477000 "07:57"
 540  540000 "09:00"
 585  585000 "09:45"
 577  577000 "09:37"
 579  579000 "09:39"
 678  678000 "11:18"
1200 1200000 "20:00"
 736  736000 "12:16"
 900  900000 "15:00"
 646  646000 "10:46"
 605  605000 "10:05"
 750  750000 "12:30"
 683  683000 "11:23"
   .       . ".:."  
 659  659000 "10:59"
 527  527000 "08:47"
 804  804000 "13:24"
 588  588000 "09:48"
 602  602000 "10:02"
 553  553000 "09:13"
 561  561000 "09:21"
 665  665000 "11:05"
 553  553000 "09:13"
end
format %tcMM:SS First_Run2
label values First_Run wanted
label def wanted 540 "09:00", modify
label def wanted 570 "09:30", modify
label def wanted 600 "10:00", modify
label def wanted 630 "10:30", modify
label def wanted 750 "12:30", modify
label def wanted 900 "15:00", modify

[Nick Cox]

You alluded to regression in #1. You could try playing with the cformat() option of regress. If this were a one-off problem and mine too, I would just do customised calculations for a table prepared with some flavour of TeX in a paper or report.

[Ben Hando]

Makes sense. Thanks, Nick. This has helped as has reviewing some of your previous answers to queries on this topic. Really appreciate it!
Ben