BookmarkSubscribeRSS Feed
KristineLeeFL
Calcite | Level 5
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.
6 REPLIES 6
polingjw
Quartz | Level 8
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]
Cynthia_sas
SAS Super FREQ
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
polingjw
Quartz | Level 8
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.
KristineLeeFL
Calcite | Level 5
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.
Cynthia_sas
SAS Super FREQ
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
KristineLeeFL
Calcite | Level 5
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.

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
  • 6 replies
  • 798 views
  • 0 likes
  • 3 in conversation