BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
elwayfan446
Barite | Level 11

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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'. 

View solution in original post

10 REPLIES 10
Reeza
Super User

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
Barite | Level 11

Both are are already date types.

 

elwayfan446_1-1586384791506.png

 

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

ballardw
Super User

@elwayfan446 wrote:

Both are are already date types.

 

elwayfan446_1-1586384791506.png

 

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
Super User

 

where date_contract_expires < datepart(date_contract_accepted);

If this does not work, post your log.

 

 

 

ballardw
Super User

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
Barite | Level 11

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
Super User

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
Opal | Level 21

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
Super User

@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
Barite | Level 11

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.

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 12433 views
  • 3 likes
  • 4 in conversation