I am outputting a dataset into excel using tagsets.excelxp.
There is a date column that is formatted as datetime9. in SAS.
I am using a proc report within the ods code to output the data.
In the excel output, the date values are like ************ although I tried with tagattr option. SAs version is 9.3.
Is there a way I can show this as date format in excel?
/**********************************************************************/
ODS tagsets.excelxp file='/usr/sas/tir/work/log_table..xls' style=statistical
options(autofilter='ALL' sheet_name='log_analysis' sheet_interval='none' embedded_titles='Yes');
title;
title1 'Log Analysis Report';
proc Report data=log_table box nowd missing out=work.log_table_final
style(header) = {background=white foreground=black FONT_WEIGHT = bold bordertopcolor=black borderleftcolor=white
borderrightcolor=white borderbottomcolor=black};
columns fname1 Date_TimeStamp1 date Status processid1 userid1 Details;
define fname1/style(header)=[background=lightgreen] width =45 display;
define Date_TimeStamp1/"Date Timestamp1" style(header)=[background=lightgreen] width =16 display;
define Date/style(header)=[background=lightgreen] f=mmddyy10. style(column)={tagattr="format:mm/dd/yyyy"};
define Status/ style(header)=[background=lightgreen] width =5 display;
define processid1/ style(header)=[background=lightgreen] width =10 display;
define userid1/ style(header)=[background=lightgreen] width =20 display;
define Details/style(header)=[background=lightgreen] width =60 display;
run;
ods tagsets.excelxp close;
ods listing;
ods listing close;
/**********************************************************************/
Stars in Excel mean that the displayed value exceeds the size of the cell. Increase the cell width, and you will see what Excel tries to display. That Excel has problems reading perfectly formatted values from even XML is no secret. It's crap, that's all.
BTW: don't name the output of tagsets.excelxp with .xls. It is XML, and the file should have an extension of .xml, for clarity. Some Excel versions will complain about that.
I increased the width as follows. But still experiencing the same issue with .xml as well.
define Date/style(header)=[background=lightgreen] width=12 f=mmddyy10. style(column)={tagattr="format:mm/dd/yyyy"};
Increase the cell width in Excel, by double-clicking the right border of the offending column.
I did manually to expanded the cell, still experiencing the same issue.
Have you already inspected the XML file with a text editor?
Yes
I found this:
http://stackoverflow.com/questions/1125036/creating-xml-for-import-into-excel-particularly-dates
Quite obviously, Excel can only recognize datetime values in a very distinct format as dates when reading from xml, everything else is treated as text.
Best solution: remove Excel from your harddisk. And create your reports as HTML to be served with a webserver. The goal is data presentation, not fixing idiotic MS bugs.
"Best solution: remove Excel from your harddisk" - That should be a stickied message on the front of the new forum.
The width statement is not the one you need to change it is this:
define Date/style(header)=[background=lightgreen] width=12 f=mmddyy10. style(column)={cellwidth=5cm tagattr="format:mm/dd/yyyy"};
^ here
Two other things to check, firstly do the dates show correctly in the XML? What is the XML row, can you paste it.
Thirdly is mm/dd/yyyy a valid Excel format, I can only find reference to mm/dd/yy.
I did ran as you said. No luck though:(
Output XML file has records like below in date variable. I also tried with format
define Date/style(header)=[background=lightgreen] width=12 f=mmddyy10. style(column)={cellwidth=5cm tagattr="format:mm/dd/yy"};
Date |
********** |
********** |
********** |
Do you actually have the stars in the XML?
Yes, I've stars in XML
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.