topic Re: Proc SQL Convert decimal to minutes and seconds (SAS) in New SAS User

Proc SQL Convert decimal to minutes and seconds (SAS)

R_Auger — Fri, 09 Apr 2021 18:07:16 GMT

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

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

PGStats — Fri, 09 Apr 2021 18:33:07 GMT

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

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

R_Auger — Fri, 09 Apr 2021 18:38:34 GMT

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.

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

PaigeMiller — Fri, 09 Apr 2021 18:49:09 GMT

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.

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

Kurt_Bremser — Fri, 09 Apr 2021 18:49:39 GMT

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;

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

PGStats — Fri, 09 Apr 2021 18:50:21 GMT

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;

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

R_Auger — Fri, 09 Apr 2021 18:51:26 GMT

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

Re: Proc SQL Convert decimal to minutes and seconds (SAS)

Kurt_Bremser — Fri, 09 Apr 2021 18:56:17 GMT

proc sql;
create table want (tm numeric format=time8.);
insert into want values (3906);
quit;