BookmarkSubscribeRSS Feed
Kalai2008
Pyrite | Level 9


How to Query date which is in format datetime20. (table1) in the proc sql case statement (Joined two tables)

case when test.log = "01MAY2015"d  then '201505'

 

Table 1 date format is datetime20.

Table 2 date format is yymmmdd10.

2 REPLIES 2
Reeza
Super User
Rather than if/then consider using an approriate format instead. Look at DATEPART to create a DATE variable from a DATETIME variable
jklaverstijn
Rhodochrosite | Level 12

The date formats are only relevant in presentation of your data. But they do tell you what the numbers mean (seconds since 01JAN1960:00:00:00 or days since 01JAN1960. Internally they are stored as a number. But they are the same values no matter what date or datetime format is associated. As a consequence a date can always be queried using a date literal like '02NOV2017'd or a datetime like ' 02JAN2017:00:00:00'dt. If you want to compare a datetime to a date use the datepart() function: WHERE datepart(dtvar)='02NOV2017'd.  In a join that could be WHERE datepart(dtvar)=datevar.

 

Hope this helps,

- Jan.

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

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
  • 1287 views
  • 2 likes
  • 3 in conversation