CODEWITHSUNDEEP
sasproc-sql
Ben
Ben

Reputation: 489

Date ranges in where clause of a proc SQL statement

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

Upvotes: 1

Views: 2989

Answers (1)

Joe
Joe

Reputation: 63424

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.

Upvotes: 3

Related Questions