BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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
4 REPLIES 4
Tim_SAS
Barite | Level 11
See page 28 of this very fine document: http://www2.sas.com/proceedings/forum2008/258-2008.pdf.
deleted_user
Not applicable
I have not seen this paper. Thanks - I will try the methods in this paper too.

LisaB
Cynthia_sas
SAS Super FREQ
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]
deleted_user
Not applicable
Cynthia - this worked GREAT!!! I had a feeling there was something I was missing. You made my week!!!

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