How to convert to date9 while using a where clause

[Maria8]

I have two columns that I need to compare the dates to. 

Column 1 is labeled as Due Date, Column 2 is Date Returned. 

I'm trying to find rows that show when the date returned is past the due date. 

Column 1's format is 08Aug2020 and Column 2's format is 03Aug2020 00:00:00. 

The code that I'm using is: 

Proc SQL; 

Create Table Past_Due As 

Select due_date, date_returned 

        date format = date9. 

Where due_date <= date_returned 

Order by due_date

from workbook; 

quit; 

There are also a lot of empty cells in date_returned - will that be something to have a condition for? 

The version is SAS EG 8.3 

Thanks! 

[LinusH]

Take a look at the datepart() function.

[Kurt_Bremser]

From what you show us, it is most likely that one variable is a date, while the other is a datetime. Which means you need to use the DATEPART function to extract the date from the datetime..

[ballardw · Accepted solution]

<Pedantic mode/ON>

Formats with SAS variables are properties for displaying values which have names. As such the phrase "Column 1's format is 08Aug2020 and Column 2's format is 03Aug2020 00:00:00." is incorrect. A format would be something like DATE9 in the first case. In the second I am not sure as the typical SAS datetime20. would not have a space between the year and the hour. You use Proc Contents to display the name of the format. Please use that to determine and share the Format assigned to Column 2 as the shown appearance for Column 2 is quite possibly Character if you do not have a custom format.

<Pedantic mode/ OFF>

---

@Maria8 wrote:

I have two columns that I need to compare the dates to. 

Column 1 is labeled as Due Date, Column 2 is Date Returned. 

I'm trying to find rows that show when the date returned is past the due date. 

Column 1's format is 08Aug2020 and Column 2's format is 03Aug2020 00:00:00. 

The code that I'm using is: 

Proc SQL; 

Create Table Past_Due As 

Select due_date, date_returned 

        date format = date9. 

Where due_date <= date_returned 

Order by due_date

from workbook; 

quit; 

There are also a lot of empty cells in date_returned - will that be something to have a condition for? 

The version is SAS EG 8.3 

Thanks! 

---

[Maria8]

Thank you! I checked and Due Date is a character rather than numeric.

How would I convert from character to numeric?

[Tom]

To convert character strings into numbers use the INPUT() function with an informat that understands how to convert the strings you have into dates.  So if DUE_DATE has values like '01JAN2020' then use the DATE9. informat.

input(due_date,date9.)

If DUE_DATE has values like '01JAN2020 00:05:00' then you might be able to use the same statement as it will ignore all but the first 9 characters.  But if some of the strings have less that 8 or 9 characters to represent the date, such as '01JAN20 00:05' then you might want to first remove the characters after the space.

input(scan(due_date,1,' '),date9.)