Formatting cells in ODS HTML

[LarrySteele]

Platform: SAS 9.1.3 on z/OS.

We use SAS to pull information from DB2, VSAM files, and other sources. A number of these fields are numbers that should be treated as characters (e.g. 9-digit Social Security Number). Most of the time, we create comma-separated files for use in Excel. The problem, of course, is that Excel strips off leading zeros fron numbers.

There are a number of work arounds for this, but I'm focusing on using ODS HTML. I've got 95% of the solution, now I'm looking for that last 5%.

Here is the ODS portion that I have:

ODS HTML FILE=EXCEL           
  HEADTEXT="<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>";
  TITLE;
  PROC PRINT DATA=EMPS_IN NOOBS;  RUN;                  
ODS HTML CLOSE;

This, of course, creates an html file. When I give it the extension XLS, Excel reads it perfectly. The HEADTEXT setting forces all cells to text. What I would like to do is set certain cells to text and set others to date, and others still to number. That's where I've hit a wall and haven't been able to work through.

I found the setting for HEADTEXT via Google:

http://64.233.167.104/search?q=cach....EXCELXP&hl=en&ct=clnk&cd=8&gl=us&lr=lang_en.

(Look for USING HTML FORMAT TO EXPORT TO EXCEL).

In the example, they show how to force all cells to text, which I've done successfully. They also show how to format different variables in different formats. When I tried their different formats suggestion, Excel reverted back to interpreting cells as it saw fit, not as I directed.

I'd appreciate if anyone has a suggestion on how to set the variable type by column. I'm oh so close.

TIA,
Larry

 

[LarrySteele]

Got it!

I made one simple change - from using ODS HTML to ODS CHTML:

ODS CHTML FILE=EXCEL                                    
  HEADTEXT="<STYLE> TD {MSO-NUMBER-FORMAT:\@}</STYLE>"; 
  TITLE;
  PROC PRINT DATA=EMPS_IN NOOBS;                        
  RUN;                                                  
ODS HTML CLOSE;

Originally I did this just to reduce the file size and rid the output of font color and size I didn't want. A side effect was the elimination of the style settings that were making all columns appear as text.

I still had to keep the HEADTEXT line. While I thought this would affect all columns, it only affected those numeric columns that were defined as characters in the source. Numeric columns that were numeric or date in the source were correctly read by Excel.

Well, it was a painful process trying to find the solution, but it's certainly a lot better than the processes we've been working with.

Cheers,
Larry