BookmarkSubscribeRSS Feed
deleted_user
Not applicable
I use ODS HTML to write a.xls file with a variable, say AGE_GP in a format 0-4, 5-9, 10-14, 15-19, ... An xls document is created, and the file is opened in Excel 2007, Excel will display the variable AGE_GP with the format 0-4, May.09, Oct.14, 15-19 not the range format 0-4, 5-9, 10-14, 15-19. How to fix it. Thanks.

FC
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
Hi:
Switching to teacher mode here for a minute...when you use ODS HTML, you are not creating a "native" or true, binary .XLS file. You are creating an ASCII text file that Microsoft Excel knows how to open and render. Even if you name the file with a .XLS extension -- the file is still HTML and ASCII text, which you can prove to yourself by opening the file with Notepad. OK...teacher clarification moment over.

Because Excel is opening an HTML table into spreadsheet view, Excel makes some assumptions (in your data's case wrong assumptions), that it should try a general numeric format for numeric columns. Because 5-9 and 10-14 looked like dates, Excel displays them as it thinks you want.

In order to "fix" the issue, you have to get Excel's attention and send it an instruction about how to format your column. With ODS HTML techniques for creating output for Excel, the method is to use the HTMLSTYLE style attribute with the MSO-NUMBER-FORMAT specification that you want. For example, if you wanted leading zeros for the variable PRODUCTID using PROC PRINT (VAR statement) or PROC REPORT (DEFINE statement), you would have:
[pre]
var productid / style(data)={htmlstyle="mso-number-format:0000000"};
define productid / style(column)={htmlstyle="mso-number-format:0000000"};
[/pre]

If you were using PRODUCTID as a CLASS variable in PROC TABULATE, then your CLASSLEV statement would be:
[pre]
classlev productid / style={htmlstyle="mso-number-format:0000000"};
[/pre]

Sometimes, Excel can be particularly frustrating -- such as with a single hyphen as you want in your string. If you run the program below, you will see that even instructing Microsoft Excel to treat the column as a TEXT format (\@), Excel ignores that format for the TESTCOL value for Alice and Alfred -- using any other separator or starting the value with a character other than space seems to finally get Excel to cooperate. Program shows using PROC REPORT, but syntax for PRINT, TABULATE is same as above.

BTW, I used the ODS MSOFFICE2K destination because ODS HTML creates HTML 4.0 tags (which Excel isn't always happy using) in favor of the Microsoft-specific HTML, as generated by ODS MSOFFICE2K. (History: When Microsoft disagreed with the W3C over the HTML specification, they formulated their own flavor of HTML.)

cynthia
[pre]
data testit;
length testcol $6;
set sashelp.class(keep=name age);
if name =: 'A' then
testcol=catx('-','5',put(age,2.0));
else if name =: 'B' then
testcol=catx('--','5',put(age,2.0));
else if name =: 'C' then
testcol=catx(':','5',put(age,2.0));
else if name =: 'H' then
testcol=catx('~','5',put(age,2.0));
else if name =: 'J' then
testcol=catx('*','5',put(age,2.0));
else testcol=catx('-','R 5',put(age,2.0));
run;

ods msoffice2k file='c:\temp\usehtmlstyle.xls' style=sasweb;

proc report data=testit nowd;
title "The \@ instructs Excel to treat the cell as TEXT";
title2 "But when you use a single - in the string does not work";
title3 "See diff between Alfred and Alice and others";
title4 "Almost any separator seems to work except a hyphen";
column name age testcol;
define name / 'Name';
define testcol / 'TestCol'
style(column)={HTMLSTYLE='text-align:left;mso-number-format:"\@"'};
run;
ods _all_ close;
[/pre]
deleted_user
Not applicable
Thank you very much.

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
  • 2 replies
  • 783 views
  • 0 likes
  • 2 in conversation