topic Re: Summarizing time columns in the VA in SAS Visual Analytics

Summarizing time columns in the VA

Sac2001 — Mon, 03 Oct 2016 19:20:05 GMT

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??

time

03:16

04:16

05:16

______

sum 12:48

Re: Summarizing time columns in the VA

BrunoMueller — Tue, 04 Oct 2016 18:56:55 GMT

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

Re: Summarizing time columns in the VA

Sac2001 — Tue, 04 Oct 2016 21:45:16 GMT

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

Re: Summarizing time columns in the VA

BrunoMueller — Wed, 05 Oct 2016 05:14:50 GMT

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

Re: Summarizing time columns in the VA

Sac2001 — Wed, 05 Oct 2016 15:41:52 GMT

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