SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

proc sql date type convertion

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

proc sql date type convertion

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


Accepted Solutions
Solution
‎07-31-2012 05:23 AM
Respected Advisor
Posts: 4,173

Re: proc sql date type convertion

Posted in reply to Nehcour0420

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;

View solution in original post


All Replies
Contributor
Posts: 73

Re: proc sql date type convertion

Posted in reply to Nehcour0420

Try convert(varchar(12), your_dtime, 108) as "HH:MMSmiley FrustratedS"

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

Super User
Posts: 19,770

Re: proc sql date type convertion

Posted in reply to Nehcour0420

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

Solution
‎07-31-2012 05:23 AM
Respected Advisor
Posts: 4,173

Re: proc sql date type convertion

Posted in reply to Nehcour0420

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;

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 13739 views
  • 6 likes
  • 4 in conversation