BookmarkSubscribeRSS Feed
Djones4822
Obsidian | Level 7

I have "number of seconds" as my measure and I'd like to convert to an HH:MM:SS 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

6 REPLIES 6
ballardw
Super User

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:MM:SS.ss (8 characters for the HH:MM:SS, 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).

 

Djones4822
Obsidian | Level 7

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

 

ballardw
Super User

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

 

Djones4822
Obsidian | Level 7

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. 

ballardw
Super User

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.

 

 

Renato_sas
SAS Employee

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

 

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
  • 6 replies
  • 1510 views
  • 0 likes
  • 3 in conversation