BookmarkSubscribeRSS Feed
R_Auger
Fluorite | Level 6

I am trying to convert decimal (seconds) to minutes seconds, so 123 decimal (seconds) would be 2:03 (2 minutes 3 seconds) or 0 hours 2 minutes 3 seconds)

I am using SAS EG 7.12 and need to do this in PROC SQL not a data step.

I have this working in MS SQL using convert and DATEADD, somehow everything I try in SAS fails, 

The duration is field is numeric.

 

Thank you

7 REPLIES 7
PGStats
Opal | Level 21

You can add your duration field expressed in seconds to any SAS datetime variable and get the correct result. That's because SAS datetimes are also expressed in seconds. But SAS also supports SAS date variables. Those are expressed in days.

 

So, the first thing to figure out is the nature of the variable to which the duration will be added. Looking at the format and range of a variable is sometimes useful in guessing what it represents. Typical values:

 

 69         data _null_;
 70         d = today();
 71         dt = datetime();
 72         put d / d date11. / dt / dt datetime20.;
 73         run;
 
 22379
 09-APR-2021
 1933597801.4
   09APR2021:14:30:01
PG
R_Auger
Fluorite | Level 6
Sorry, the field type is Numeric and is not a date/datetime it is session duration and is shown as 123, 456, 743 which are decimals that represent seconds, 123 seconds, 456 seconds etc. I need to show 2:03 (2 minutes 3 seconds) in SQ I am using Convert and it works, in Proc SQL I tied suing PUT/INPUT and it is not working.

PaigeMiller
Diamond | Level 26

Do you really need to convert 123 to 2:03 (a text string with a colon in it)? Or can you leave the 123 as 123 and then format it so that it appears as 2:03. The latter would be incredibly simple.

--
Paige Miller
R_Auger
Fluorite | Level 6
If it can be done in formatting, I am ok with that. I still need to account for decimals that would be great enough for example to be 1 hour 5 min and 6 seconds
There is not calculation done on this field
Kurt_Bremser
Super User

SAS times are stored in numeric variables with a time format assigned. SAS times and datetimes are counts of seconds, the proof can be seen here:

data have;
duration = 123;
run;

proc sql;
create table want as
select duration, duration as time format=time8.
from have;
quit;
PGStats
Opal | Level 21

If all you need is to display the duration with a given format then give it that format in proc SQL. Here I use the mmss. format

 

proc sql;
select 
    123 as duration format=mmss.,
    count(*) as dummy
from sashelp.class;
quit;

PGStats_0-1617994209604.png

 

PG

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

Mastering the WHERE Clause in PROC SQL

SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 7 replies
  • 2771 views
  • 4 likes
  • 4 in conversation