Help using Base SAS procedures

How to convert date9. to datetime20. or vice versa using proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

How to convert date9. to datetime20. or vice versa using proc sql

How to convert date9. to datetime20. or vice versa using proc sql.

Could somebody please help me with that?

 

Thanks Smiley Happy 


Accepted Solutions
Solution
‎02-16-2018 08:00 AM
Super User
Posts: 13,563

Re: How to convert date9. to datetime20. or vice versa using proc sql

[ Edited ]

The FORMAT only matters for display so the question is transform from date to datetime or datetime to date. If you are going to Create a datetime value from a date you need a time part. If you have no specific time you could use 0:0:0 to be midnight. That would be:

 

proc sql;

   create table want as

   select *, DHMS(date,0,0,0) as datetime format=datetime20.

   from have;

run;

To extract the date from a datetime:

Proc Sql;

   create table want as

   select *, datepart(datetime) as date format=date9.

   from have;

run;

 

If you think of just changing the display it will not work as SAS datetime values are recorded in seconds and dates are in Days. So the functions DHMS and Datepart are the main ways to do transforms. You can specify the time if you have actual hour, minute second value by DHMS(date, hour, minute,second). If you have a separate actual time value then DHMS(date,0,0,time) as time should be the correct number of seconds.

 

View solution in original post


All Replies
Solution
‎02-16-2018 08:00 AM
Super User
Posts: 13,563

Re: How to convert date9. to datetime20. or vice versa using proc sql

[ Edited ]

The FORMAT only matters for display so the question is transform from date to datetime or datetime to date. If you are going to Create a datetime value from a date you need a time part. If you have no specific time you could use 0:0:0 to be midnight. That would be:

 

proc sql;

   create table want as

   select *, DHMS(date,0,0,0) as datetime format=datetime20.

   from have;

run;

To extract the date from a datetime:

Proc Sql;

   create table want as

   select *, datepart(datetime) as date format=date9.

   from have;

run;

 

If you think of just changing the display it will not work as SAS datetime values are recorded in seconds and dates are in Days. So the functions DHMS and Datepart are the main ways to do transforms. You can specify the time if you have actual hour, minute second value by DHMS(date, hour, minute,second). If you have a separate actual time value then DHMS(date,0,0,time) as time should be the correct number of seconds.

 

Community Manager
Posts: 3,432

Re: How to convert date9. to datetime20. or vice versa using proc sql

@ballardw shared the proper approach when you need to transform a date or datetime value to perform a comparison or join within a query.  If you need a value simply for reporting though -- using a procedure other than PROC SQL -- you can use a format to "convert" a date or datetime variable into a category.  See this blog post about date and datetime formats for more information.

☑ This topic is solved.

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

Discussion stats
  • 2 replies
  • 15535 views
  • 7 likes
  • 3 in conversation