Hi:
When I do this:
[pre]
data chm_client_level;
infile datalines dsd;
input cl_n
CL_EFF_FRM_DT : date7.
CL_EFF_TO_DT : mmddyy8.
TestNum;
return;
datalines;
001,15nov50,11/15/08,123456
002,23aug51,09/01/08,123456
003,01jan63,12/31/08,123456
;
run;
ods listing;
ods msoffice2k file='c:\temp\CHM_ClientLevel.xls' style=minimal;
proc print data=WORK.CHM_CLient_Level label noobs;
var CL_N / style(data)={htmlstyle='mso-number-format:@'};
var CL_EFF_FRM_DT CL_EFF_TO_DT /
style(data)={htmlstyle='mso-number-format:mm\/dd\/yy'};
var TestNum;
format CL_EFF_FRM_DT CL_EFF_TO_DT mmddyy8. TestNum 2.;
run;
ods _all_ close;
[/pre]
I get a formatted date in the Excel file -- with a 2 digit year. I also get a formatted date in the SAS output with a 2 digit year because of the format statement. No matter how I read the data, the SAS format is generally only used to produce the SAS output file. With LISTING or HTML in a browser, you don't have any issue. But, in general, SAS date or currency formats are not respected by Excel when it opens the HTML file and puts the numbers in spreadsheet cells. Just because you give the file an XLS extension does not mean that SAS is creating an Excel file. It just means that you are giving your HTML file an extension that will cause Excel to launch when the file is double clicked. To verify that the file is still an HTML file, open your "XLS" file with Notepad or a text editor and you will see that SAS and ODS are writing HTML tags.
So, that means you have to use HTML methods (CSS style properties) to format the numbers in a way that Excel can use. Just like you are formatting your CL_N to be a number, I use mso-number-format for my HTMLSTYLE attribute value to send the Excel format for the date. I use mso-number-format instead of 'vnd.ms-excel.numberformat:@ -- just because I've had better experience with it and all my examples are coded using mso-number-format. In my example the [pre] \/ [/pre] is NOT the capital V it is the 2 slashes backslash \ then forward slash / together with no spaces in between.
Generally, in SAS output (not in Excel) asterisks are a sign that a format is too small. In this instance, if you run the above code, you WILL see asterisks in Excel for the TestNum variable and you also get this message in the log:
[pre]
NOTE: At least one W.D format was too small for the number to be printed. The decimal may be shifted by the "BEST" format.
[/pre]
This is because the format of 2. is too small for the variable TestNum. If I increase the format for TestNum to 6., then the asterisks in Excel would go away, because SAS would have a big enough format to use on the number.
cynthia