Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Formatting cells in ODS HTML

Formatting cells in ODS HTML

Formatting cells in ODS HTML

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:


  PROC PRINT DATA=EMPS_IN NOOBS;  RUN;                  

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:;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.



RE: Formatting cells in ODS HTML

Got it!

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


ODS CHTML FILE=EXCEL                                    
  PROC PRINT DATA=EMPS_IN NOOBS;                        

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.


Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close