The SAS Output Delivery System and reporting techniques

ODS HTML Excel 2007 changes dates

Reply
New Contributor
Posts: 4

ODS HTML Excel 2007 changes dates

Anyone else experienced this? We use ODS HTML to write a .xls file with a date field column formatted yymon7, for example May-09. When the html opens in excel 2007, it looks fine. But if anything is modifed and saved, the field instead becomes 5-9-2010. I've even tried creating a character field to hold the month/year, and it still converts to a date in 2010, converting the month to month, year to day, and adding 2010 as the year. If we do a file save as in excel, and save it to excel 97-2003, or as an xlsx, then it is ok. Also, if keeping as file type html, it creates a whole folder of html output, filelist, sheet001, stylesheet, tabstrip. This did not happen in 9.1.3, with excel 2003.
Regular Contributor
Posts: 171

Re: ODS HTML Excel 2007 changes dates

Posted in reply to KristineLeeFL
I'm sure that there are more technically correct ways to do this, but one really easy solution is to append an invisible character onto the date. You can type invisible characters into the program editor by turning on Num Lock, holding down the Alt key, typing the number 255, then releasing the Alt key. Try running the following program. Note that you will need to recreate the invisible character in your SAS session before you submit the program. Both dates look the same the first time Excel is opened. If you make a change, save, and reopen excel, however, the value for date1 changes and the value for date2 does not. Hope this helps.

[pre]
data temp;
date1 = '01may2009'd;
date2 = cats(substr(propcase(put(date1, monyy5.)), 1, 3),'-',substr(put(date1, monyy5.),4,2), ' '); /*The last argument is an invisible character created with Alt+255*/
format date1 monyy7.;
run;

ods listing close;
ods html file='C:\temp.xls';
proc print data=temp;
run;
ods html close;
[/pre]
SAS Super FREQ
Posts: 8,866

Re: ODS HTML Excel 2007 changes dates

Posted in reply to KristineLeeFL
Hi:
YYMON7. format should give you 2009MAY as the formatted display of the date value, NOT May-09. And the MONYY7. format would display as: JAN2009, not May-09. So that's confusing.

However, aside from format confusion, you are correct that if you do anything to add to or alter the HTML output, as sent from SAS, then Excel uses its default internal formats for any data you type into the worksheet.

The behavior you note when you save as HTML (the whole folder of HTML output) is the default HTML save behavior that Excel uses. If you want a SINGLE web page, then choose the MHTM or MHTML format when saving. When you save to binary .XLS format or as the new .XLSX format, then you are forcing Excel to convert from the HTML file created by SAS and ODS to a "true" Excel format for the file.

You might try using the HTMLSTYLE attribute to send a Microsoft format for the date from SAS to Excel -- but the issue may still remain when you change or copy or alter the HTML file -- that Excel uses the default format for any "new" cells. This would be a question for Tech Support.

Alternately, if you have Office 2003, you might want to investigate the use of ODS TAGSETS.EXCELXP to create Spreadsheet Markup Language XML files that Excel can open and render. This web site shows how to use the TAGATTR attribute to send a Microsoft format from SAS to Excel:
http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

cynthia
Regular Contributor
Posts: 171

Re: ODS HTML Excel 2007 changes dates

Posted in reply to Cynthia_sas
Just to clear up the confusion... If a variable is assigned a format of monyy7, a document with an xls extention is created with the html destination, and the file is opened in Excel 2007, Excel will display the variable with the format May-09, not MAY2009.
New Contributor
Posts: 4

Re: ODS HTML Excel 2007 changes dates

Posted in reply to KristineLeeFL
Thanks. I did type out the month and year example incorrectly. We will take a look at these options. We don't have office 2003, only 2007.
SAS Super FREQ
Posts: 8,866

Re: ODS HTML Excel 2007 changes dates

Posted in reply to KristineLeeFL
Hi:
TAGSETS.EXCELXP output can be opened with Excel 2003, Excel 2007 and Excel 2010. It cannot be opened with Excel 2000 or Excel 97. What I should have typed was "if you have Excel 2003 or higher".

cynthia
New Contributor
Posts: 4

Re: ODS HTML Excel 2007 changes dates

Posted in reply to KristineLeeFL
Thanks. Tagsets.excelxp is the direction we were going to go in, so that this would work consistently for either our folks with office 2003, or office 2007.
Ask a Question
Discussion stats
  • 6 replies
  • 227 views
  • 0 likes
  • 3 in conversation