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;
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.
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.