Date ranges in where clause of a proc SQL statement

Question

There is a large table containing among other fields the following: ID, effective_date, Expiration_date.

expiration_date is datetime20. format, and can be NULL

I'm trying to extract rows that expire after Dec 31, 2014 or do not expire (NULL).

Adding the following where statement to the proc sql query gives me no results

where coalesce(datepart(expiration_date),input('31/Dec/2020',date11.))
> input('31/Dec/2014',date11.);

However, when I only select NULL expiration dates and add the following fields:

put(coalesce(datepart(expiration_date),input('31/Dec/2020',date11.)),date11.) as value,
put(input('31/Dec/2014',date11.),date11.) as threshold,
case when coalesce(datepart(expiration_date),input('31/Dec/2020',date11.)) > input('31/Dec/2014',date11.) 
    then 'pass' else 'fail' end as tag

It shows 'pass' under TAG and all the other fields are correct.

This is an effort to duplicate what I used in SQL Server

where isnull(expiration_date,'9999-12-31') > '2014-12-31'

Using SAS Enterprise Guide 7.1 and while trying to figure it out I've been using

proc sql inobs=100;`

What am I doing wrong ? Thank you.

Some Expiration Dates:

30OCT2015:00:00:00
30OCT2015:00:00:00
29OCT2015:00:00:00
30OCT2015:00:00:00

Couple of pointers. You don't need to use `input` there; you can use date constants. `"31DEC2014"d` is a date constant and directly represents the date. Second, it would be helpful to see the values for `value` and `threshold`. Third, what happens if you bring the data into SAS (with a select) and then apply the same filter? It's possible something is being mistranslated in the pass-through query. — Joe, Apr 17 '17 at 21:16
Also - I assume you're connecting to SQL Server to run this query? If you are, and you're using `libname` to connect, try adding `DIRECT_SQL=NONE` to the libname (see http://support.sas.com/documentation/cdl/en/acreldb/63647/HTML/default/viewer.htm#a002679005.htm) and see if that fixes things - if so post back and I'll make this an answer. — Joe, Apr 17 '17 at 21:20
Using "31DEC2014"d solved the problem. I'm curious as to why. — Ben, Apr 18 '17 at 13:11
Date functions are often poorly translated when passthrough occurs. — Joe, Apr 18 '17 at 13:12

score 3 · Accepted Answer · answered Apr 18 '17 at 13:13

I would recommend using a date constant ("31DEC2014"d) rather than date functions, or else either use explicit passthrough or disable implicit passthrough. Date functions are challenging when going between databases and so avoiding them when possible is best.

Date ranges in where clause of a proc SQL statement

1 Answers1