The SAS Output Delivery System and reporting techniques

ods tagsets.excelxp proc report datetime9. column displays as text in excel

Reply
Super Contributor
Posts: 428

ods tagsets.excelxp proc report datetime9. column displays as text in excel

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;

/**********************************************************************/

Super User
Posts: 7,371

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 428

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

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"};

Super User
Posts: 7,371

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

Increase the cell width in Excel, by double-clicking the right border of the offending column.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 428

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

I did manually to expanded the cell, still experiencing the same issue.

Super User
Posts: 7,371

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

Have you already inspected the XML file with a text editor?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 428

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

Yes

Super User
Posts: 7,371

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

try

style(column)={tagattr="type:date"}

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,371

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Super User
Posts: 7,668

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

"Best solution: remove Excel from your harddisk"  - That should be a stickied message on the front of the new forum.

Super User
Super User
Posts: 7,668

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

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.

Super Contributor
Posts: 428

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

I did ran as you said. No luck thoughSmiley Sad

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
**********
**********
**********
Super Contributor
Posts: 428

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

Please see this:

Cynthia provided solution for a similar issue.However the only difference is 'date' variable is created with datetime9. format in my scenario.

Super User
Posts: 7,371

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

Do you actually have the stars in the XML?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 428

Re: ods tagsets.excelxp proc report datetime9. column displays as text in excel

Yes, I've stars in XML

Ask a Question
Discussion stats
  • 26 replies
  • 917 views
  • 3 likes
  • 7 in conversation