Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Summarizing time columns in the VA

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 5
Accepted Solution

Summarizing time columns in the VA

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


Accepted Solutions
Solution
‎10-06-2016 05:02 PM
SAS Super FREQ
Posts: 703

Re: Summarizing time columns in the VA

 

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

Capture.PNG

 

Give it a try

 

Bruno

 

 

View solution in original post


All Replies
Solution
‎10-06-2016 05:02 PM
SAS Super FREQ
Posts: 703

Re: Summarizing time columns in the VA

 

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

Capture.PNG

 

Give it a try

 

Bruno

 

 

Occasional Contributor
Posts: 5

Re: Summarizing time columns in the VA

[ Edited ]

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

SAS Super FREQ
Posts: 703

Re: Summarizing time columns in the VA

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

Occasional Contributor
Posts: 5

Re: Summarizing time columns in the VA

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

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 428 views
  • 0 likes
  • 2 in conversation