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.
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.
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.;
ods listing close;
ods html file='C:\temp.xls';
proc print data=temp;
ods html close;
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
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.