BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
ashwini2
Fluorite | Level 6

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

Could somebody please help me with that?

 

Thanks 🙂 

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

2 REPLIES 2
ballardw
Super User

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.

 

ChrisHemedinger
Community Manager

@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.

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 46235 views
  • 9 likes
  • 3 in conversation