Format proc report to output dates in excel using ods excel

[Kia_B]

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: 

ods excel file="\\xxxmyfilelocation\Events_%sysfunc(date(),yymmddd10.).xlsx" style=forNESUG options (FLOW='Tables');

ods excel options (zoom='80' sheet_name='Tracker');

proc report data=tracker nowd split= "_" style(column)=[JUST=LEFT VJUST=TOP FONT=(Calibri, 11pt, NORMAL) BORDERSTYLE=SOLID BORDERCOLOR=black]

style(header)=[BACKGROUNDCOLOR=light grey FONT=(Calibri, 11pt, BOLD) JUST=CENTER VJUST=TOP  BORDERSTYLE=SOLID BORDERCOLOR=black];

column Initial_Code_Date Initial_Attribution_Date Initial_SO_Coverage Date_of_AE_status_Evaluated Initial_Rep_event_became_SAE Type_of_SAE Final_attribution Study_RX_held Treatment_rechallenged Treatment_Permanently_Discontinu Status_AE  SO_FU_Log_Comments AF_summary_comments Final_MedDRA_code Final_MedDRA_LLT FINAL_Code_Date Final_Attribution_Date FU_SO_Coverage Initial_site_email_archived_  Final_site_email_archived_ Completed_pharma_reporting_ Closed SO_closed_AE Close_Date Teaching_example Tracker_run_date;

define Initial_Code_Date/'Initial Code Date' style={background=lightyellow color=black};  

define Initial_Attribution_Date/'Initial Attribution Date' style={background=lightyellow color=black}; 

define FINAL_Code_Date/'Final Code Date'  style={background=lightyellow color=black}; 

define Final_Attribution_Date/'Final Attribution Date' style={background=lightyellow color=black};  

define SO_Log_Comments/'SO Log Comments' style(column) = [just=left cellwidth=3in background=lightyellow color=black] style(header)=Header{background=lightyellow color=black};  

define SO_FU_Log_Comments/'SO FU Log Comments' style(column) = [just=left cellwidth=3in background=lightyellow color=black] style(header)=Header{background=lightyellow color=black};   

define Reas_NonReportable_AE/'Reas NonReportable AE' style(column) = [just=left cellwidth=0.5in background=lightyellow color=black] style(header)=Header{background=lightyellow color=black};

define Teaching_example/'Teaching example' style={background=lightyellow color=black};

define Closed/'Closed' style={background=lightyellow color=black}; 

define SO_closed_AE/'SO closed AE' style={background=lightyellow color=black}; 

define Close_Date/style={background=lightyellow color=black}; 

options missing=" ";

format Initial_Code_Date date11. Initial_Attribution_Date date11. Final_Code_Date date11. Final_Attribution_Date date11. Close_Date date11.;

run; 

I think the issue is my define statement, because the last block of code which exports to a different sheet in the excel workbook exports the date correctly and there is no define statement. 

proc report data=sites nowd split= "_" style(column)=[BACKGROUNDCOLOR=white JUST=LEFT VJUST=TOP FONT=(Calibri, 11pt, NORMAL) BORDERSTYLE=SOLID BORDERCOLOR=black]
style(header)=[BACKGROUNDCOLOR=light grey FONT=(Calibri, 11pt, BOLD) JUST=CENTER VJUST=TOP BORDERSTYLE=SOLID BORDERCOLOR=black];
column SiteNo Name Status Country Status_Date Note;
format Status_Date date11.;
run;

Any suggestions? 

[PaigeMiller]

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

[Kia_B]

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. 

[PaigeMiller]

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.

[Kia_B]

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: 

[PaigeMiller]

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;

[Kia_B]

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.

[Tom]

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:

• datetime values (number of seconds) displayed with the DTDATE9. format
• Character variables with strings that look like what the DATE9.  format would produce.

[Kia_B · Accepted solution]

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?