Hello,
I have a table that displays dates in the format "01JAN2020:12:00:00" and I'm trying to pull that data into a newly created table using proc sql without the time. I've done a bit of research on this and can't seem to get the syntax correct (using SELECT CONVERT) and an error when trying to run the query in SAS Enterprise Guide 7.1. Any ideas on how I might be able get rid of time?
Thanks!
Use format DATETIME7. or DATETIME9.
Please note this is not a DATE format as your title and text imply. These are date/time formats which are not the same as DATE formats and should not be confused with DATE formats.
Date/Time formats must be used when your variable is a date/time variable, which your variable is (obviously). Date formats cannot be used on date/time variables, and vice versa.
Here is a list of all date, datetime and time formats in alphabetical order, each one clearly states whether it is a date format or a datetime format or a time format. https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=allprodslang&docsetTarget=syn...
HI @S0MBR0 Assuming your date is a numeric SAS Datetime value displaying the value using the format Datetime. , the values would of course look like "01JAN2020:12:00:00"
Should you require a Date value from a datetime value, You would need to use Datepart function to extract the date value.
select datepart(datetime_var) as new_date format=whatever date format
Use format DATETIME7. or DATETIME9.
Please note this is not a DATE format as your title and text imply. These are date/time formats which are not the same as DATE formats and should not be confused with DATE formats.
Date/Time formats must be used when your variable is a date/time variable, which your variable is (obviously). Date formats cannot be used on date/time variables, and vice versa.
Here is a list of all date, datetime and time formats in alphabetical order, each one clearly states whether it is a date format or a datetime format or a time format. https://documentation.sas.com/?cdcId=pgmmvacdc&cdcVersion=9.4&docsetId=allprodslang&docsetTarget=syn...
@gpai - This worked perfectly, thank you so much! I also appreciate the link you provided, it will be a good resource for me going forward.
In SAS (and most database systems), there is a fundamental difference between dates and times/datetimes.
Dates are stored as counts of days (allowing the use of only 4 numeric bytes), while times and datetimes are stored as counts of seconds (in the full 8-byte real format). That's why you need a special format for displaying only the date part of a datetime, or the datepart() function to extract the date out of a datetime value.
@Kurt_Bremser - Thank you for the information, I did not fully grasp the difference between Date and DateTime but your explanation makes a lot of sense.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
SAS' Charu Shankar shares her PROC SQL expertise by showing you how to master the WHERE clause using real winter weather data.
Find more tutorials on the SAS Users YouTube channel.