Comparing a SAS Datetime value to a SAS Date Value

[elwayfan446]

I am trying to do a very basic comparison in sas and for the life of me can't figure out what I am doing wrong.

I have two variables I want to compare... one is a datetime and the other is just a date.  I need to run a query where the date variable is less that the datetime variable, but because they are not both one or the other, it doesn't compare it properly.  I can't simply format it, I need it to be the actual format of the variable in the database in order to compare.

where mmddyy10. < datetime20.

I just can't figure out how to convert one or the other.  If I use datepart() on the datetime value, I get a SAS date in which I still can't compare to a date that is formatted in mmddyy10. and I can't figure out how to convert that sas date into a mmddyy10. either.  If I try to use dhms() on the date variable, I also get a sas value that is not comparable.

[Reeza]

What are the TYPES (not formats) of each variable you're trying to compare?

---

@elwayfan446 wrote:

I am trying to do a very basic comparison in sas and for the life of me can't figure out what I am doing wrong.

I have two variables I want to compare... one is a datetime and the other is just a date.  I need to run a query where the date variable is less that the datetime variable, but because they are not both one or the other, it doesn't compare it properly.  I can't simply format it, I need it to be the actual format of the variable in the database in order to compare.

where mmddyy10. < datetime20.

I just can't figure out how to convert one or the other.  If I use datepart() on the datetime value, I get a SAS date in which I still can't compare to a date that is formatted in mmddyy10. and I can't figure out how to convert that sas date into a mmddyy10. either.  If I try to use dhms() on the date variable, I also get a sas value that is not comparable.

---

[elwayfan446]

Both are are already date types.

I am running a query where I want to find records where the date_contract_expires < date_contract_accepted

[ballardw]

---

@elwayfan446 wrote:

Both are are already date types.

I am running a query where I want to find records where the date_contract_expires < date_contract_accepted

---

No they aren't Date_contract_accepted is DATETIME. The units used in datetime values are seconds. The unit used in Dates are Days.

https://communities.sas.com/t5/SAS-Communities-Library/Working-with-Dates-and-Times-in-SAS-Tutorial/... has a PDF with much information about dates.

[Reeza]

where date_contract_expires < datepart(date_contract_accepted);

If this does not work, post your log.

[ballardw]

You can extract the date portion of a Datetime variable with the DATEPART function:

where datevar < datepart (datetimevar)

The display formats have nothing to do with the numeric value comparisons. The functions and comparisons use the internal values.

[elwayfan446]

As I mentioned, when I do that it gives me a result in a sas date format.  I can't (or don't know how) to change the date to an actual mmddyy10. format inside the database, not just simply the label.

[Reeza · Accepted solution]

Formats don't matter for comparison, SAS will compare the underlying values. There should be no need to set the format of the variable. 

If you want to format the date after the fact you can always do that.

data_new = datepart(datetimeVar);
format date_new mmddyys10.;

if date_new > date_old then ....

---

@elwayfan446 wrote:

As I mentioned, when I do that it gives me a result in a sas date format.  I can't (or don't know how) to change the date to an actual mmddyy10. format inside the database, not just simply the label.

---

What are you using to show types/formats/ SAS only has two types, num/char, so I'm confused as to where it would say 'Date'. 

[Patrick]

SAS stores both Date and DateTime values as numbers in a numerical variable (no special data type like in databases).

A SAS Date value is the count of Days since 1/1/1960

A SAS DateTime value is the count of Seconds since 1/1/1960

A SAS Format is used for Display ("print") of values so they become human readable. A SAS Format does not change the value as such.

There are formats for DateTime values and there are formats for Date values.

Calculations and comparisons are done using the Internal values (the counts of days or seconds). Formats are of no relevance here.

The datepart() function converts a SAS DateTime value to a SAS Date value (basically: Integer portion of a division of the DateTime value through seconds of a day).

[ballardw]

---

@elwayfan446 wrote:

As I mentioned, when I do that it gives me a result in a sas date format.  I can't (or don't know how) to change the date to an actual mmddyy10. format inside the database, not just simply the label.

---

Does the comment "inside the database" imply that your data resides in an external database such as Oracle, DB2 or SQL Server?

If so you need to provide a lot more detail such as how you are connecting to the database and which one as that can have a serious affect on date related issues.

[elwayfan446]

My problem was that I was getting what I thought was an incorrect result based on the actual comparison of the values.  After digging through my code and all of the relevant dates I had and also thinking more about what you guys were telling me, I realized what I did wrong.  If nothing else, your responses got my mind pointed in the right direction to figure this out.

Thank you for all the help.  At least this thread will help someone else when they have questions regarding the formatting versus the actual value stored in the dataset.