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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 703 views
  • 2 likes
  • 3 in conversation