topic Re: PROC SQL condition on datetime date in SAS Programming

PROC SQL condition on datetime date

newboy1218 — Fri, 07 Feb 2020 16:42:10 GMT

Hi, in my proc sql statement, I want to apply a where condition on the date variable. The date variable has datetime20. format, for example: 17APR2016:07:57:23

My code is

where (snapshot_creation_date <= '2019-12-01' and snapshot_creation_date >= '2019-12-31')

But I got the error saying:

ERROR: Expression using greater than or equal (>=) has components that are of different data
types.

What can I change to make this works? Thanks!

Re: PROC SQL condition on datetime date

ballardw — Fri, 07 Feb 2020 16:50:09 GMT

To compare SAS date values to a literal date the format must be a date9 or date7 value quoted and followed with a d such as '01DEC2019'd. Second you would want to compare the DATEPART of a datetime value

For example to select records in Dec of 2019 you might use:

where ('01DEC2019'd <= datepart(snapshot_creation_date) <= '31DEC2019'd)

note that your original code would only return values >= 31Dec2019 if it had worked.

SAS will allow doing a sequential comparison without having to do an "and" with two separate ranges.

Re: PROC SQL condition on datetime date

ed_sas_member — Fri, 07 Feb 2020 16:54:39 GMT

Hi @newboy1218

Have you tried this, assuming the date variable is a SAS Date with the format you specified:

where (datepart(snapshot_creation_date) >= '01JAN2019'd and datepart(snapshot_creation_date) <= '31DEC2019'd)

NB: please check the condition (<= / >=)

Best,

Re: PROC SQL condition on datetime date

Tom — Fri, 07 Feb 2020 16:56:00 GMT

The problem is because you asked SAS to compare a number (datetime values are the number of seconds since 1960) to a string.

To make a datetime literal you need to use a string that the DATETIME informat can read with the letters DT after the closing quote.

Also what do you want to do with values like: '01DEC2019:10:30'dt or '31DEC2019:09:30'dt?

Perhaps you just want to do:

where snapshot_creation_date < '02DEC2019:00:00'dt
  and snapshot_creation_date >= '01JAN2020:00:00'dt