We use ODS to HTML to accomplish this trick. We then import the *.htm
file into Excel and the formatting comes relatively intact. You can use
PROC TEMPLATE to make the HTML output look the way you want and then the
Excel spreadsheet will retain those characteristics. E.g.
Data test ;
Array var ;
Do i=1 to 20 ;
Do j=1 to 5 ;
var[j] = ranUni(76867168) ;
Ods html file="C:\My Documents\SasTesting\test.html" ;
Proc print data=test ;
Ods html close ;
creates an html file called test.html. You can then open Excel and select
OPEN from the FILE drop-down menu. Select Files of type: HTML Documents.
Then open your file (test.html).
Is this close to what you want?
Edward Heaton, SAS Senior Statistical Systems Analyst,
Westat (An Employee-Owned Research Corporation),
1550 Research Boulevard, Room 2018, Rockville, MD 20850-3195
Voice: (301) 610-4818 Fax: (301) 294-3992
From: Brittain, James [mailto:zqr0@CDC.GOV]
Sent: Monday, April 16, 2001 12:14 PM
Subject: Loading a SAS Dataset to MS Excel w/ formatted cells?
Is there a way to load data into a MS Excel spreadsheet and have control
over the cell formatting, not the data formats, like BOLD, SHADING, and
I was have been loading data into Excel by either creating a .CSV file and
opening it with Excel or directly creating .XLS using SAS/ACCESS DBLOAD
procedure. The problem is that once I have the data in the spreadsheet I
have to go in and format the cells the way I want them to look. Usually I
add gridlines, make the labels bold, shade some cells, modify the width of
columns, and change cells to "wrap text". There are times that I have to
create multiple spreadsheets that need to look the same but have different
I was thinking that I could create an Excel template and call it with DBLOAD
or create an empty spreadsheet the way I want it and APPEND to it but from
what I understand APPEND is not available with the XLS DBMS in SAS/ACCESS.
I would think that something along this line would be the idea because I do
not see and SAS syntax for the DBLOAD procedure that allows for formatting
the cells like (row_1 = BOLD, row_2 = NOBOLD).
Another thing that I noticed is when I use the DBLOAD procedure with long
character variables it is truncating them to $200. Is this because the
DBLOAD (.XLS) procedure has not been updated to the long character variables
Thanks in advance for any and all help,