The SAS Output Delivery System and reporting techniques

ODS Excel text problems

Reply
N/A
Posts: 0

ODS Excel text problems

I am using SAS 9.2, and Excel 2003 for this task. I have 400 observations that my boss wants in excel worksheet with a blank line after every 5th observation. This was a simple task, so we used ODS with a print statement. Below is the code:

ods html file = "C:\testing files\laboutput %sysfunc(today(),date9.).xls"
Headtext="";

ods noptitle;
options pageno=1 orientation=landscape;


PROC PRINT data= foura NOOBS BLANKLINE=5 LABEL SPLIT='*';
VAR NEWID /style={HTMLCLASS = 'Zero'};
VAR VISITDATE ASSESSVISITDATE;
LABEL VISITDATE='Weekly*VisitDate'
ASSESSVISITDATE='*Med*AssessDate';

FORMAT NEWID $13. ;
RUN;
ODS HTML CLOSE;
RUN;

When looking at the dataset, NEWID is a character variable that is a mix of numeric and character strings. Below are my actual NEWID values along with the way it looks in my excel output:

NEWID in data~~~~~~NEWID in excel
111142900202~~~~~~1.11143E+11
1111214A0203~~~~~~1111214A0203
1111216D0103~~~~~~1111216D0103
111121400202~~~~~~1.11121E+11
1111715E0106~~~~~~1.11E+112

The string that have both letters and numbers are being displayed correctly, but the strings that have only numbers OR include an E are being displayed in Scientific Notation format.

I’m at a loss, and need some experienced SAS help here!! I want NEWID to show as text, and hopefully I’ve done something wrong in my code. When I click in the excel cells, the correct values are being shown in the formula bar. I’ve tried different formats in excel, and nothing is working completely. The values that already include an E are looking really messy!!

I’m open to any direction.

LisaB
Super Contributor
Posts: 394

Re: ODS Excel text problems

See page 28 of this very fine document: http://www2.sas.com/proceedings/forum2008/258-2008.pdf.
N/A
Posts: 0

Re: ODS Excel text problems

I have not seen this paper. Thanks - I will try the methods in this paper too.

LisaB
SAS Super FREQ
Posts: 8,744

Re: ODS Excel text problems

Hi:
Welcome to the world of Excel doing what it wants with your output from SAS. What you have discovered is that Excel will use a general number format -- if your variable is a character variable in the SAS dataset and even if you have a SAS report that shows the number correctly (NOT in scientific notation).

There is a fix for this, but the fix involves using STYLE= overrides. If you are creating output with HTML-based methods, such as ODS HTML or ODS MSOFFICE2K (preferred because it is "Microsoft flavor of HTML") , then your STYLE= override would reference the HTMLSTYLE= attribute. If you are creating XML based output, such as with ODS TAGSETS.EXCELXP, then your STYLE= override would reference the TAGATTR= attribute.

I see that you are trying to use a HEADTEXT= to pass in a custom number format... I don't know whether Excel will actually respect CSS passed in this way. It was with INLINE styles that Microsoft disagreed with the W3C on HTML 4 -- that's why they came up with their own flavor of HTML.

I'd recommend that you try the direct approach shown below, using a destination that Microsoft likes AND using the direct approach with the text format. And, you can also refer to this Tech Support note that has code that shows the use of HTMLSTYLE with the @ value for the mso-number-format style property.
http://support.sas.com/kb/32/414.html (do note that the example uses MSOFFICE2K for the HTML file -- which is what I would recommend) Microsoft "likes" its own type of in-line style and I'm not sure it will use an HTMLCLASS in the same way that a standard browser will use HTMLCLASS. I think HTMLSTYLE is the better way to go.

cynthia
[pre]
ods msoffice2k file = "C:\testing files\laboutput %sysfunc(today(),date9.).xls" ;

ods noptitle;

PROC PRINT data= foura NOOBS BLANKLINE=5 LABEL SPLIT='*';
VAR NEWID /style={htmlstyle="mso-number-format:\@"};
VAR VISITDATE ASSESSVISITDATE;
LABEL VISITDATE='Weekly*VisitDate'
ASSESSVISITDATE='*Med*AssessDate';
FORMAT NEWID $13. ;
RUN;

ods msoffice2k close;
[/pre]
N/A
Posts: 0

Re: ODS Excel text problems

Cynthia - this worked GREAT!!! I had a feeling there was something I was missing. You made my week!!!
Ask a Question
Discussion stats
  • 4 replies
  • 211 views
  • 0 likes
  • 3 in conversation