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

Create an hh:mm:ss aggregate-able value

Reply
Contributor
Posts: 32

Create an hh:mm:ss aggregate-able value

I have "number of seconds" as my measure and I'd like to convert to an HH:MMSmiley FrustratedS format. 

 

I noticed that SAS doesn't have an equivalent to oracle's numToDSInterval so I looked around and found the TimeFromHMS except I only have seconds.

 

First I tried passing (0, 0, sec_var) but that didn't seem to work right. Then I tried calculating each digit value using MOD() and FLOOR()

 

But this never seemed to work right either. For some reason MOD(seconds, 60) does nothing and returns values well above 60. 

 

Does anyone know a way I can create this variable for my reports that will also be "aggregatable" so that I can show "total hh:mm:ss" on my charts?

 

Thanks

Super User
Posts: 13,876

Re: Create an hh:mm:ss aggregate-able value

[ Edited ]
Posted in reply to Djones4822

Probably a good idea to provide some better concrete idea of what the actual result is supposed to look like for some specific values.

 

If a value is indeed seconds you may only need to apply a time format:

 

format variablename time8. ; will show number of seconds as hh:mm:ss.

data junk;
   t = hms(34,1,23);
   format t time8.;
run;

for instance.

 

If you need to display fractions of second then make the overall length larger and add the number of decimals: time11.2 will show HH:MMSmiley FrustratedS.ss (8 characters for the HH:MMSmiley FrustratedS, 1 for the decimal point and 2 decimals: length 11.2).

 

You can then use variations of the format to create groups: Proc freq and most analysis procedures will honor a TIME2. format to group data by hour.

The functions INTNX and INTCK can increment or return intervals of time values (as well as date and datetime).

 

Contributor
Posts: 32

Re: Create an hh:mm:ss aggregate-able value

Thanks for the reply @ballardw

 

However, I'm having trouble incorporating your advice into SAS VA. When I use TIME8. in my EG scripts I am returned a categorical variable that can't be used as a measure.

 

Is there some format that I could use that preserves the numeric type of the duration so that I can place it in charts and use aggregations? 

 

The data is literally "duration" measured in seconds. I want it displayed in hh:mm:ss 

sec         format_sec
0        -> 00:00:00
60       -> 00:01:00
110      -> 00:01:50 
1700     -> 00:28:20
4040     -> 01:07:20
----------------------------
SUM      -> 01:48:40

 

Super User
Posts: 13,876

Re: Create an hh:mm:ss aggregate-able value

Posted in reply to Djones4822

@Djones4822 wrote:

Thanks for the reply @ballardw

 

However, I'm having trouble incorporating your advice into SAS VA. When I use TIME8. in my EG scripts I am returned a categorical variable that can't be used as a measure.

 

 

 


I have no experience with VA and how "roles" such as categorical are assigned or inspected I do not know.

Did the variable have that role PRIOR to assigning the format? That would likely explain the behavior.

Can you show code used to assign the format? Did you by chance create a TEXT version of the values by using Put with the format (in some form)?

 

Contributor
Posts: 32

Re: Create an hh:mm:ss aggregate-able value

My apologies, I thought this was the VA specific community for these types of questions...

 

The variable is stored in an oracle DB table where the field is numeric. There is no code to show, VA has a built in "calculated field" feature that allows you to use pre-compiled SAS functions to produce new fields. In this case I called TimeFromHMS() to produce the categorical measure. 

 

Using Enterprise Guide I can execute more traditional SAS code, where using PUT(<var>, Time8.) yields a "time" datatype field, but I cannot use it as a measure, only a category. 

 

This is all fairly specific to the VA environment (we are on 7.4), fyi. 

Super User
Posts: 13,876

Re: Create an hh:mm:ss aggregate-able value

Posted in reply to Djones4822

It is the VA area but I had noticed that you hadn't gotten any response and thought I'd take a stab at helping. Some times the commonality across SAS works.

 

NOW we have ORACLE. That might have helped at the beginning. And might still have problems.

 

I would suggest trying without the TimeFromHMS call and just read the value as it comes from Oracle. Then see what might be needed. If you get an integer such as 3640 and think that should be 1 hour and 40 seconds then maybe setting format to that value is all that is needed.

 

That function expects three parameters, an hour value, a minute value and a second value such as

TimeFromHMS(17, 15, 23) returns 05:15:23 PM. I have no idea what that would do attempting to read an Oracle value though.

 

 

SAS Super FREQ
Posts: 98

Re: Create an hh:mm:ss aggregate-able value

Posted in reply to Djones4822

Hi @Djones4822,

 

Support for elapsed time (time8.) and other duration formats were added in the latest release, VA 8.3. Prior to that release, I'm not aware of any other way for aggregating number of seconds and displaying it as hh:mm:ss, other than showing three separated columns for hours, minutes, and seconds. Here is an example using VA 8.3:

 

VA83ElapsedTimeExample.PNG

 

Ask a Question
Discussion stats
  • 6 replies
  • 123 views
  • 0 likes
  • 3 in conversation