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
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
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.
proc sql;
create table want (tm numeric format=time8.);
insert into want values (3906);
quit;
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;
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;
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.
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.