Smart questions
Smart answers
Smart people
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Member Login




Remember Me
Forgot Password?
Join Us!

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips now!
  • 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!

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

Donate Today!

Do you enjoy these
technical forums?
Donate Today! Click Here

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.
Jobs from Indeed

Link To This Forum!

Partner Button
Add Stickiness To Your Site By Linking To This Professionally Managed Technical Forum.
Just copy and paste the
code below into your site.

LarrySteele (Programmer)
10 Apr 07 18:03
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:

CODE

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=cache:qH-EGwF0yCwJ:www.nesug.org/html/Proceedings/nesug05/dp/dp2.pdf+cell+attributes+TAGSETS.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 (Programmer)
11 Apr 07 10:36
Got it!

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

CODE

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

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