Help formatting dates

[RandoDando]

I need help. I imported an excel file with a date which shows as 07/31/2026 in the data after import. It is in numeric date format. I want it as 31-JUL-2026. I’ve tried input(date, date11.). But that returns null. Ive tried format date date11. But they still display as 07/31/2026. How can I get them as 31-JUL-2026?

[FreelanceReinh · Accepted solution]

Hello @RandoDando,

Your suggested FORMAT statement should work in a DATA step and also in a PROC DATASETS step as shown below:

/* Create sample data for demonstration */

data have;
date='31JUL2026'd;
format date mmddyy10.;
run;

/* Change the date format */

proc datasets lib=work nolist;
modify have;
format date date11.;
quit;

Replace work and have with your libref and dataset name, respectively.

If your code doesn't change the format, please post the log (using the "Insert Code" button).

[RandoDando]

Thanks. I have a followup question: will Proc compare treat two identical date values in different formats as equal?

[Tom]

---

@RandoDando wrote:
Thanks. I have a followup question: will Proc compare treat two identical date values in different formats as equal?

---

Yes.  The value stored is not impacted by the method you have attached for how to display the value.

[ballardw]

---

@Tom wrote:

---

@RandoDando wrote:
Thanks. I have a followup question: will Proc compare treat two identical date values in different formats as equal?

---

Yes.  The value stored is not impacted by the method you have attached for how to display the value.

---

An anecdote from data imported from Excel: I have had values that appeared as dates in Excel that had a decimal component that was kept on import. Since the SAS date formats only use the the integer portion you might not see that decimal bit until you try to match the dates and get unexpected results. 

IIRC this occurred with an Excel column that contained mixed date only values and datetime values as Excel uses the decimal portion to store time of day information.

[FreelanceReinh]

---

@RandoDando wrote:
will Proc compare treat two identical date values in different formats as equal?

---

It will just mention the format difference in the output, but treat the values as equal.

...
Number of Variables with Differing Attributes: 1.


Listing of Common Variables with Differing Attributes

Variable  Dataset    Type  Length  Format

date      WORK.WANT  Num        8  DATE11.
          WORK.HAVE  Num        8  MMDDYY10.
...
Number of Observations with All Compared Variables Equal: 1.

NOTE: No unequal values were found. All values compared are exactly equal.

[byhal]

Hi!  How about this?

DATA example;
d = INPUT("07/31/2026", mmddyy10.);
FORMAT d date11.;
RUN;

[Tom]

---

@RandoDando wrote:
I need help. I imported an excel file with a date which shows as 07/31/2026 in the data after import. It is in numeric date format. I want it as 31-JUL-2026. I’ve tried input(date, date11.). But that returns null. Ive tried format date date11. But they still display as 07/31/2026. How can I get them as 31-JUL-2026?

---

INPUT() is used with an INFORMAT to convert text into values.  It will not work properly with a numeric variable since SAS will first have to convert the number into some text since that is what the INPUT() function needs.

If the variable is numeric with the MMDDYY10. format attached so the values print in the mm/dd/yyyy style you show then just change it so that the variable has the DATE11. format attached to it so the dates print in the dd-MMM-yyyy style you want.  So just use a FORMAT statement as part of another data step (or as part of PROC MODIFY code to change the format attached to the current variable).

format date date11.;

If the variable is character then you could either make a new numeric variable and attach the desired format to it. 

newdate = input(date,mmddyy10.);
format newdate date11.;

Or make a new character variable with the dates in the style you want.

newdate=put(input(date,mmddyy10.),date11.);