Where DATETIME20. variable=ddmmyyyy

[Justin9]

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:34, 31MAR202212:12:20:35.

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

[Tom · Accepted solution]

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;