topic Re: How to convert to date9 while using a where clause in SAS Enterprise Guide

How to convert to date9 while using a where clause

Maria8 — Mon, 24 Jan 2022 07:11:50 GMT

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!

Re: How to convert to date9 while using a where clause

LinusH — Mon, 24 Jan 2022 07:43:29 GMT

Take a look at the datepart() function.

Re: How to convert to date9 while using a where clause

Kurt_Bremser — Mon, 24 Jan 2022 07:48:30 GMT

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

Re: How to convert to date9 while using a where clause

ballardw — Mon, 24 Jan 2022 16:12:31 GMT

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.

@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!

Re: How to convert to date9 while using a where clause

Maria8 — Mon, 24 Jan 2022 17:15:30 GMT

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

How would I convert from character to numeric?

Re: How to convert to date9 while using a where clause

Tom — Mon, 24 Jan 2022 17:37:38 GMT

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