I am attempting to output an excel (xlsx) file using ods excel from a proc output. My variables are in date9. format in the SAS data set, but when they output to excel they are in a text format.
This is my code:
I found when I remove the "cellwidth= " from the style (column) statement, then the date displays correctly in excel. Does anyone know why this might be?
It's hard for me to understand the two blocks of code you show. There is no variable named STATUS_DATE in the first block of code. However, in the first block of code, for example, you include the format in the DEFINE statement, like this:
define FINAL_Code_Date/'Final Code Date' style={background=lightyellow color=black} format=date9.;
My code outputs multiple sheets to an excel workbook. The first code is where the issue is. The second code is just an example showing that sheet is exporting correctly with the correct date format. Since the second code is working correctly, I was wondering if the issue was in my define statement in the first code. There is no define statement in the second code. The format=date9.; output the variable as a character variable in excel.
When I use format=date9. in a DEFINE statement, the variable is recognized as a valid date in Excel.
If it is not recognized as a valid date for you, please show us actual code where the DEFINE statement includes format=DATE9. and also a screen capture of Excel where we can see clearly that this is a character variable.
Please use the "insert photos" icon to include your screen capture in your reply. Do NOT attach files.
Here are the dates in Excel.
here is the code with the format. I did date11.
Here is the second part of my code that is working correct without the define statement
Excel table shows a date:
I can't reproduce this, so I cannot say why you are getting this problem. When I try it both ways, they wind up as numeric dates in Excel.
data a;
date='01JAN2024'd;
run;
ods excel file="test.xlsx";
proc report data=a;
columns date;
define date/"Date" display format=date11.;
run;
ods excel close;
ods excel file="test2.xlsx";
proc report data=a;
columns date;
define date/"Date" display;
format date date11.;
run;
ods excel close;
Thank you. I think it must be related to the style statements in the define row, but I'm not sure why those would cause an issue. Those are the only differences I see in my code when the date displays, verses when it doesn't.
My variables are in date9. format in the SAS data set
SAS datasets have only two types of variables, floating point numbers and fixed length character strings.
A FORMAT is something you attach to a variable to control how it displays.
Check the variables in question and make sure they are of type numeric with the DATE format attached. You will need to use PROC CONTENTS or other method of checking the dataset and not just look at the printed results.
Other ways to have values that look like were produced by the DATE9. format are:
I found when I remove the "cellwidth= " from the style (column) statement, then the date displays correctly in excel. Does anyone know why this might be?
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.