Hi,
I have a table with a date field whose values are in this form:
14JUL2023:11:27:52.619374
10JAN2024:10:58:13.665456
20JAN2021:19:23:40.697598
28DEC2022:12:23:56.123211
18JAN2017:11:13:11.259936
I would like to know how to use this field in a SQL query, to select data from 2022 and 2023 for example.
Thanks
First, a significant detail, that value is a datetime value, not a date. This is somewhat important to keep track of in SAS as Dates are numbers of days from 1 Jan 1960 and datetimes are numbers of seconds from midnight on that date. If you want to select date related intervals from datetime you would use the DATEPART function to extract the date from the datetime value for use in comparisons. You might further use other functions on that extracted date such as Year, Month others as needed.
Comparing variable values to specific dates that you specify, called a date literal, you must provide the date information in the code as a quoted date9 (7 works but I detest 2 digit years for ambiguity) appearing value suffixed with the letter D to tell SAS you intend it to be used as a date.
example to select values in those years :
proc sql; create table output as select * from yourdataset where year(datepart(datetimevariable)) between 2022 and 2023 ; quit;
or specific dates:
proc sql; create table output as select * from yourdataset where datepart(datetimevariable) between '15Jan2022'd and '23Feb2023'd ; quit;
Since date values are numeric the typical comparisons for less than or greater than work but do want the date literal if the desired comparison value is not in a variable.
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.
First, a significant detail, that value is a datetime value, not a date. This is somewhat important to keep track of in SAS as Dates are numbers of days from 1 Jan 1960 and datetimes are numbers of seconds from midnight on that date. If you want to select date related intervals from datetime you would use the DATEPART function to extract the date from the datetime value for use in comparisons. You might further use other functions on that extracted date such as Year, Month others as needed.
Comparing variable values to specific dates that you specify, called a date literal, you must provide the date information in the code as a quoted date9 (7 works but I detest 2 digit years for ambiguity) appearing value suffixed with the letter D to tell SAS you intend it to be used as a date.
example to select values in those years :
proc sql; create table output as select * from yourdataset where year(datepart(datetimevariable)) between 2022 and 2023 ; quit;
or specific dates:
proc sql; create table output as select * from yourdataset where datepart(datetimevariable) between '15Jan2022'd and '23Feb2023'd ; quit;
Since date values are numeric the typical comparisons for less than or greater than work but do want the date literal if the desired comparison value is not in a variable.
https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.
Thank you !
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9.
Early bird rate extended! Save $200 when you sign up by March 31.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.