06-29-2015 02:55 PM
I am trying to display the datepart of a date column in proc report and format it as mmddyy10.. The column currently has the hour minute second:
My code looks like this:
Proc Report data = infile;
define cut_id / format = $10.;
define sale_date / format = mmddyyd10.;
When I run this code the sale_date column looks like this in Excel:
How do I have the sale_date column formatted as mmddyy10?
Thank you very much for any input you may have!
06-29-2015 03:04 PM
I would recommend having a separate DATA step before your REPORT procedure that creates a new variable using the datepart() funciton. Example code below, hope this helps!
proc report data=infile_new;
define cut_id / format=$10.;
define saledate / format=mmddyy10.;
06-29-2015 05:41 PM
But another issue is HOW is the output being sent to Excel. Sometimes, Excel has its own way of displaying dates, leading zeroes, etc. So the format you WANT, isn't always the format you GET in Excel because Excel does its own thing. This frequently happens when you are using ODS HTML or ODS MSOFFICE2K at the output destination to create output for Excel. With ODS TAGSETS.EXCELXP and SAS 9.4, I don't notice as many issues with Excel and dates but sometimes I have issues with Excel and column widths or decimals or leading zeroes.
06-29-2015 06:21 PM
Thank you dcruik and Cynthia.
The sas runs on unix and uses tagsets.excelxp. It's supposed to be an automated process without manual intervention. I went ahead with dcruik's suggestion to change the column format in an earlier dataset. It seems to have worked.
Thank you both for your input.