Filtering by date proc sql

[jffeudo86]

Hi,

I have the following variable:

Variable	Type	Len	Format	Informat
DUEDATE	Num	8	DATETIME20.	DATETIME20.

With the following query:

proc sql inobs=10;
select duedate from output.mydata;
quit;

I show 23MAY2018:12:10:56 in the first row. But when I query using the outputted data, no rows are returned.

proc sql ;
select * from output.mydata
where duedate = '23MAY2018:12:10:56'd;
quit;

Ultimately I want my where clause to be duedate between '01APR2019'd and '30APR2019'd, but right now it's not returning anything so I tried the above.  Please help.

[jffeudo86 · Accepted solution]

Got it now, use datepart:

where datepart(duedate) = '23MAY2018'd.

[Tom]

So you appear to have a DATETIME variable instead of a DATE variable.  Dates are stored as a number of days and datetime values as a number of seconds.

You also seem to have found a strange feature of SAS.  If you add a time part to the literal string in a DATE literal the time part is ignored when converting the string into an actual date value.  It doesn't even had to look like a time value, it can any extraneous text because SAS just ignores it once it has 9 characters to convert into a date value.

540  data test;
541   duedate = '23MAY2018:12:10:56'd;
542   put duedate= :comma15. ' ' duedate date9. ' ' duedate datetime20.;
543   duedate = '23MAY2018:blahblah'd;
544   put duedate= :comma15. ' ' duedate date9. ' ' duedate datetime20.;
545  run;

duedate=21,327  23MAY2018   01JAN1960:05:55:27
duedate=21,327  23MAY2018   01JAN1960:05:55:27

So if you want to make an actual DATETIME literal remember to use DT suffix after the quoted string and not just the letter D.

546
547  data test;
548   duedate = '23MAY2018:12:10:56'dt;
549   put duedate= :comma15. ' ' duedate date9. ' ' duedate datetime20.;
550  run;

duedate=1,842,696,656  *********   23MAY2018:12:10:56

Notice how the actual value of the DATETIME value is so much larger than the DATE value.  That is because there are 24*60*60=86,400 seconds in a single day.