Proc compare for dates

[Akshaya_1397]

Hi,

I have two datasets oldfile and newfile and it has two date variables exp_date and create_date

Oldfile -exp_date  has value 12/31/9999 and create_date has values 03/01/2022

Newfile-expdate has value 31dec9999:00:00:00 and create_date has value 01mar2022:00:00:00

I need to compare both the values so I changed all the date values to date9. Format

Data oldf;

Set oldfile;

Format create_date date9.;

Run;

Data newf;

Set newfile;

Create_date1=dhms(create_date,0,0,0);

Format create _date1 datetime19.;

Create_date1=datepart(create_date);

Format create_date1 date9.;

Drop create_date;

Rename create_date1=create_date;

Run;

Even though it is showing as difference in the proc compare as shown below is there any other reason behind it.

[PaigeMiller]

PROC COMPARE and really all SAS PROCs work on the unformatted values. That's why PROC COMPARE thinks these are different. The formatting is irrelevant.

If you want PROC COMPARE to find these to be the same date, you need to use the DATEPART() function on the newfile variable EXPDATE before you run PROC COMPARE.

expdate=datepart(expdate);

[Akshaya_1397]

Thankyou for ur reply. just now I have edited my post if you can see for create_date I have formatted ro date9.

And in newfile I have used the datepart(create_date) too but still getting those as difference.

[PaigeMiller]

From now on, please don't change your original post in substantive ways (its okay to correct spelling or grammar mistakes, but not data or code) as now it becomes impossible to follow the thread.

But anyway, I don't see your PROC COMPARE statement. Please provide code (the same code you put in the original message, all of it, plus the PROC COMPARE) by clicking on the "little running man" icon and pasting your code into the window that appears.

[Quentin]

From the compare output, it looks like you have differences that are less than one.

Remember that SAS date variables are just numeric variables.  The value is the number of days since January 1, 1960.  To see these values more clearly , you could use a FORMAT statement on the PROC COMPARE step to format create_date as 8.2.  That won't change the results of the COMPARE, but it will display the values in the output as a number with two places after the decimal point.  After you see that value, you can either investigate where this decimal value came from (sometimes when importing a date-time variable from an external source into a SAS date, the time part becomes a partial day).  Or you could just decide to use ROUND() or CEIL() or FLOOR() to get rid of the decimal part, then try the PROC COMPARE again.

[Tom]

The FORMAT used to DISPLAY the values does not change the value that is stored.

So to convert a DATETIME value to a DATE value you need to use the DATEPART() function (or just divide by the number of seconds in a day).  You can then use any format that displays dates with the new DATE valued variable.

The differences in your photograph mean that one or the other of your two variables as non-integer values.  The DATE format ignores that fraction of day when displaying the date.

Remove it with the INT() function.