BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
sasuser_8
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

View solution in original post

2 REPLIES 2
ballardw
Super User

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.

sasuser_8
Obsidian | Level 7

Thank you !

sas-innovate-white.png

Register Today!

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.

Register now!

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 906 views
  • 0 likes
  • 2 in conversation