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!
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.
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.
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,
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.