BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12

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;

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

26 REPLIES 26
Kurt_Bremser
Super User

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.

Babloo
Rhodochrosite | Level 12

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

Babloo
Rhodochrosite | Level 12

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

Kurt_Bremser
Super User

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Babloo
Rhodochrosite | Level 12

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
**********
**********
**********
Babloo
Rhodochrosite | Level 12

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 26 replies
  • 1811 views
  • 3 likes
  • 7 in conversation