topic Changing Date Format in Proc SQL in New SAS User

Changing Date Format in Proc SQL

S0MBR0 — Wed, 29 Jan 2020 16:24:57 GMT

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!

Re: Changing Date Format in Proc SQL

novinosrin — Wed, 29 Jan 2020 16:30:13 GMT

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

Re: Changing Date Format in Proc SQL

PaigeMiller — Wed, 29 Jan 2020 16:47:50 GMT

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=syntaxByCategory-format.htm&locale=en#p0aa41u6lidfz1n1n977se5xgl2s

Re: Changing Date Format in Proc SQL

Kurt_Bremser — Wed, 29 Jan 2020 17:06:47 GMT

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.

Re: Changing Date Format in Proc SQL

S0MBR0 — Wed, 29 Jan 2020 18:42:10 GMT

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

Re: Changing Date Format in Proc SQL

S0MBR0 — Wed, 29 Jan 2020 18:42:56 GMT

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