topic Re: proc sql date type convertion in SAS Data Management

proc sql date type convertion

Nehcour0420 — Mon, 30 Jul 2012 19:01:35 GMT

Hi,

I'm developing something using proc sql and just wondering if there is any way to convert a DATETIME25.6 type into a TIME8.0.

eg.

From 28MAR2010:08:34:14.596704 to 8:34:14

Thanks,

Eric

Re: proc sql date type convertion

robby_beum — Mon, 30 Jul 2012 19:21:51 GMT

Try convert(varchar(12), your_dtime, 108) as "HH:MM:SS"

where the varchar(??) is what your value is.

Re: proc sql date type convertion

Reeza — Mon, 30 Jul 2012 19:38:05 GMT

You can use the sas function timepart as well, timepart(your_variable) format=time8.

Re: proc sql date type convertion

Patrick — Tue, 31 Jul 2012 09:23:54 GMT

If your data lives in SAS datasets then there are only 2 variable types: character and numeric. There is no such thing like a type DATETIME25.6 or TIME8.0.

What there is are numeric variables containing a SAS date, datetime or time value (date: number of days since 1Jan1960, datetime: number of seconds since 1Jan1960, time: number of seconds since midnight).

So as these are all numbers it's only about assigning an appropriate format to "print" the values so that they become human readable (showing us the number as a date string).

If you start with a datetime value (a number representing the seconds since...) and you only want to print the time part then you could use format TODw.d

If you actually want to change the internal value from a SAS datetime to a time value then use timepart() to convert the number and then use a time format like time8. for printing.

data _null_;;
var='12aug2012:09:08:07'dt;
put var= var= tod.;
var='09:08:07't;
put var= var= time8.;
run;

data have;
dt='12aug2012:09:08:07'dt;
run;

proc sql;
select dt format=best32., dt format=datetime., dt format=tod.
from have;
quit;