Format Excel date as dd/mm/yyyy

[labin]

I have 3 datetimes in my dataset in the following format: 02AUG2017:07:39:00.000

I want to output them to Excel through ODS using a format 08/02/2017.  I have tried several date formats but haven't been succesfful.  What am I missing?

ods EXCEL file="\\networkpath_&SYSDATE..xlsx" ; 

ods EXCEL options(sheet_name='Negative Margin Audit');
proc print data=work.AP_Audit_NegMgn3

style (header) = {background=cyan}
style (grandtotal) = {background=cyan} LABEL;
format order_margin movement_margin total_charge Carrier_Pay Carrier_Extra_Pay Carrier_Total_Pay dollar10.2;
format Delivery_Date ddmmyys10.;
sum order_margin;
sum movement_margin;
sum total_charge;
sum carrier_pay;
sum carrier_extra_pay;
sum carrier_total_pay;
run;

ods EXCEL close;

[ballardw · Accepted solution]

There are three basic approaches. The first is to realize that you have a DATETIME value and DATE formats do not work as datetime is measured in seconds and dates in days.

Easiest if you are willing to accept values like 02AUG2017 is to use the datetime format DTDATE9.

Second would be to create a custom format to show the date pieces of a datetime value. If you are going to do this, display datetimes in an mmddyy10 format (not ddmmyy which would show 2 aug as 02/08/2017), frequently might be the way to go.

Third is to create an actual date value in your data set. If you don't use the time portion for anything then in a data step:

Delivery_date = datepart(Delivery_date);

If you need to keep the datetime value then create a new variable:

D_date = datepart(Delivery_date);

and then use the mmddyys10. format with the variable.