BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Sac2001
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
BrunoMueller
SAS Super FREQ

 

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

4 REPLIES 4
BrunoMueller
SAS Super FREQ

 

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

 

 

Sac2001
Calcite | Level 5

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
SAS Super FREQ

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
Calcite | Level 5

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

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.

Find more tutorials on the SAS Users YouTube channel.

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