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

Hi,

 

I am creating a table below and have a where clause, where I want to select observations where the DATE variable (which has a DATETIME20. format) is on the day of 31 Mar 2022. Please can someone correct my code (specifically the line of code where the where clause is, as my code is currently returning zero observations) that I have included below?

 

For example, in the examples below, the code would keep the last three observations (highlighted in red below) because they are within the day of 31 Mar 2022:

There will be observations in the DATE variable, such as 01MAR202211:12:20:33, 01MAR202211:12:20:34, 01MAR202211:12:20:35, 31MAR202212:12:20:33,31MAR202212:12:20:3431MAR202212:12:20:35.

 

proc sql;
    create table checks as
    select date
              , value
    from ac.database
    where ppn_dt="31Mar2022"d ;
run;

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

DATETIME values are number of seconds.  DATE values are number of days.

 

Just convert the seconds into days, then your equality test will work.  SAS has a function to make it easier called DATEPART().

where datepart(ppn_dt)="31Mar2022"d ;

 

If you are pulling from a remote database you might get better performance by not requiring the function call.  Instead test for a range of seconds (datetime values). 

where "31Mar2022:00:00"dt <= ppn_dt < "01APR2022:00:00"dt;

View solution in original post

1 REPLY 1
Tom
Super User Tom
Super User

DATETIME values are number of seconds.  DATE values are number of days.

 

Just convert the seconds into days, then your equality test will work.  SAS has a function to make it easier called DATEPART().

where datepart(ppn_dt)="31Mar2022"d ;

 

If you are pulling from a remote database you might get better performance by not requiring the function call.  Instead test for a range of seconds (datetime values). 

where "31Mar2022:00:00"dt <= ppn_dt < "01APR2022:00:00"dt;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
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
  • 1 reply
  • 772 views
  • 0 likes
  • 2 in conversation