Summarizing time columns in the VA

[Sac2001]

Time and date columns are treated as categories , hence summrization is not possible. The only solution is convert the time column into numeric and use them as measure and get summary total. but the total look useless because its all numeric values, i want to see total in hh:mm format. 

I'm trying to change the format of time column to elapsed time and summarizing them in the bottom. The other reporting tool allows summarizing of time column, not sure why it's not supported in SAS . Is there a work around??

eg 

time

03:16

04:16

05:16

______

sum 12:48

[BrunoMueller · Accepted solution]

Hi

yes a time value is a category value and therefor you can not do any aggregations.

I suggest to do the follwoing:

create a new calculated data item that represents the time as a numeric value using the TreatAs operator, something like:

TreatAs(_Number_, 'Time'n)

Then you create a new aggreated measure using this expression:

Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 )

where 'timeSec'n is the calculated item from the first step

Essentialy you compute the minutes from you seconds and then use the Mod operator to add the remaining minutes (divided by 100) to the result. This will give you the proper result as mm.ss,, see also picture

Give it a try

Bruno

[Sac2001]

Thanks Bruno, 

I see it produce results in mm:ss, is there way to include hh:mm:ss as well, i have seconds in hours as well. like timesec = 4284 and i want to convert into 1:11:24 and summarize in bottom

Thanks for your input

[BrunoMueller]

One could use the same technique for hours as well, but I guess the formatting of the result will be difficult, since there is no numeric format that with ":" as separator

Bruno

[Sac2001]

Hey Bruno, thanks for your input. this is what i use to convert sec into min and min into hr. 

hourMinMsr=

If Sum [_ByGroup_] ('timeSec'n) <=3600 return

  Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 )

else 

  (Floor(( Sum [_ByGroup_] ('timeSec'n) / 60 )) + ( ( Sum [_ByGroup_] ('timeSec'n) Mod 60 ) / 100 ))/60

The above scenario would display both sec in hours and min but user would have difficulty interpreting the data and differentiating between min and hours. To represent the data accurately, i think we can only use sec in min or sec in hour to accurately display the results. To combine sec in both min or hr will lead to more confusion. Let me know if you think otherwise or better solution. thanks. 

TimeCat    timesecNum  MinMsr   hourMsr   hourMinMsr

0:06:04     364                6.04        0.10         6.04

1.11.24     4284              71.24      1.19         1.19