proc sql date type convertion

[Nehcour0420]

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

[robby_beum]

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

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

[Reeza]

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

[Patrick · Accepted solution]

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;