Output SAS Results in HTML (Excel) Files Using ODS

Updated by Dan Blanchette on March 16, 2004:
"(Excel)" was added to page to highlight the fact that Excel easily opens html files (without having to use the import wizard!). A trick of SAS users of ODS is to simply make the file extension *.xls rather than *.html even though the file is really an html file. Since most users are more interested in making tables in Excel, I wanted to highlight this feature to draw them in.

Presented by: Dan Blanchette

This document contains:
-- A friendly intro on how to create output in html files

-- The bare minimum example of SAS code to create output in html files

-- Where SAS puts the html file

-- How to stop outputting to html files

-- A good example of SAS code to create output in html files

-- A better example of SAS code to create output in html files

-- The unfortunate reality of output from a proc means

-- Other ways to get output into an Excel spreadsheet

A friendly intro on how to create output in html files

Starting with SAS version 7 (SAS7) it is possible to make SAS output web pages (*.html or *.htm suggested file extensions). The contents of your program's output that is saved in the .lst file can also be saved as an html file. This html file can be immediately viewed by a web browser (Netscape or Internet Explorer). The real advantage with this is that Excel, MS-Word, and Word Perfect all read in html files with all the fancy table formats that you see with a web browser. This allows you to take your SAS output and very quickly and easily put the results into nice and manageable tables for a report or paper to be published. It also automates the process so that re-runs go faster and more accurately. What I'm talking about is SAS's Output Delivery System (ODS). It outputs SAS data sets or html files and many more funky things. For now, let's stick with using it to output html files. ODS also gives you more control of your output. It allows you to break up the long .lst file into many html files if that makes your life easier. I'm not going to show you examples of that but I just want you to know that it's possible. NOTE: ODS does not affect what output goes into the .lst file.

Once your html file is in Excel or MS-Word, etc. save the file as a spreadsheet or document in order to get rid of the html formatting code. After that, format the document as you desire using the knowledge you have of that software. A reason for not going straight from SAS to Excel is that titles cannot be kept in the process of moving from SAS to Excel. Outputting to HTML allows for the titles to go along with the results.

You need to check ODS output with your output window/.lst file. So far I have not seen problems related to html output.

A web browser can view a file on your hard drive or on the CPC network (when you're logged into the CPC network). Files on Gromit and many other Linux boxes are not viewable from your PC's browser. Copy them to CPC or ask CPChelp@unc.edu to copy them to the CPC webserver (where you are now).

All text in this web page that looks like this can be 
 copied and pasted directly into your program!

The bare minimum example of SAS code to create output in html files

This creates the least formatted html file with the least amount of code.

*** Create HTML files in the default directory **;
ods html file="probit_html.html"
        style=minimal;

proc probit data=in.csf9634;
class gender;
  model gender=d2dqi_r age d2enersf;
run;

ods html close;

(see example output)

Back to top

Where SAS puts the html file

If you are running interactive SAS on the Windows network, you can find SAS's default file location by clicking the open folder icon. Most likely your html output file will not be in the directory where your program or your data set is located. For this reason I suggest that you specify exactly where you want your html file to be saved.

ods html file="c:\SASoutput\probit_html.html"
        style=minimal;

If you are running SAS in batch then the default file location is the directory you submitted the batch program.

If you are using interactive SAS on UNIX or Linux then the default file location is the directory where you invoked SAS to start interactive SAS.

LESSON: Better to not rely on defaults. Specify where you want your html file to be saved.

Back to top

How to stop outputting to html files

WARNING: If you are using SAS7/SAS8 interactively, SAS will continue to add output to these files until you close ODS output to html.

** Close the HTML destination. **;
** When using SAS interactively you must close the destination before
   you can browse the HTML files. **;

ods html close;

NOTE: The next two examples create all the files needed to view the output in FRAMES.

If you want to see all 3 windows at once view the frames.html file.

If you only want the output then view the body.html file.

A good example of SAS code to create output in html files

*** Create HTML files in the directory in which the
     program was submitted. ***;
ods html file="body.html"     /** the name of the body file **/
        style=minimal         /** name the format style of the output **/
         path="./" (url=none) /** name the location to store these files **/
     contents="contents.html" /** name the contents file **/
         page="page.html"     /** name the page file **/
        frame="frame.html";   /** name the frame file **/

(see example output)

Note: SAS has to be told to do this statement before you want it done. This statement comes before the proc print you want to go into the html files. A good place to put this is right after your libname statement at the top of your program. Also notice this is all one (1) statement so only put a semicolon (;) on the last line. The nice thing is that you only need the ODS statement once in your program. From there on out it will add all output to those files. If you want output to go to different html files you need to add another ODS statement before the procedure that creates the output. When creating a frames version of your output, make sure "(url=none)" is typed after the path section of the statement. If it is not, it is possible that the browser will not recognize all the files that go into each window.

A better example of SAS code to create output in html files

This ODS statement allows you to name any of the files whatever name you want. However, you should use the file extension ".htm" or ".html". In my programs I use macro vars to make the creation of these files easy and automatic:

%let date=November 17, 1999; ** useful since the html documents
                                don't have the date automatically 
                                printed like the .lst and
                                .log files do **;
%let progname=test; ** change it once and use it throughout the program **;
%let root=/project;
%let job=paper/progs;
libname in V7 "&root./&job./";

*** Create HTML files ***;
ods html file="&progname._b.html"
        style=minimal
     headtext="<title> Test results in frames.</title>"
         path="&root./&job./" (url=none)
     contents="&progname._c.html"
        frame="&progname._f.html"
         page="&progname._p.html";

proc print data=&progname.;
 title "data=&progname. : ODS made simple";
 title2 " &date. ";
run;

(see example output)

*** "Headtex=" is put in this program so that your html
    file will have a document title. 
    If this file is bookmarked, the title will appear
    as the text in your bookmark list.  ***;

The unfortunate reality of output from a proc means

Unfortunately, ODS HTML output from a proc means puts each column into one cell. This means that the table squishes the cell contents all around when the table is not wide enough (which is often the case). When Excel reads in proc means html output it splits these large cells appropriately. MS-Word leaves them as is. To remedy this I suggest outputting a data set from the proc means and then printing it with proc print. This can also be helpful if you are trying to make one table from more than one procedure's output. With ODS available there are now 2 ways to output a data set from a procedure. My first 2 examples show you ways that could be done with any version of SAS.

Example 1 (close):

** This outputs results as a SAS data set that looks somewhat like
   the regular printed output. **;
proc means data=test noprint;
output out=mean_test1;
run;

proc print data=mean_test1 noobs;
run;

(see example output)

Example 2 (closer):

** This outputs a data set of the results that looks almost exactly like
   the regular printed output **;
proc means data=test noprint;
output out=mean_test2(drop=_type_ _freq_);
run;

proc transpose data=mean_test2 out=mean_test2
(rename=(col1=n col2=min col3=max col4=mean col5=std));
run;

proc print data=mean_test2 noobs;
run;

(see example output)

*** same idea but with a by statement added **;
proc means data=test noprint;
by gender;
output out=mean_test2(drop=_type_ _freq_);
run;

proc transpose data=mean_test2 out=mean_test2
(rename=(col1=n col2=min col3=max col4=mean col5=std));
by gender;
run;

proc print data=mean_test2 noobs;
by gender;
run;

(see example output)

Example 3 (perhaps):

Use ODS to output a data set.

** This outputs a data set with all the results on the same line **;
quit;  /* If you add a quit statement before using ODS output, 
          it is less likely you'll encounter a weird error message. */

ods output summary=mean_test3;

proc means data=test;
run;

ods output close;  ** stops output to the SAS data set work.mean_test3 **;

ods html file="mean_test3.html";

proc print data=mean_test3;
run;

ods html close;

(see example output)

Back to top

Other ways to get output into an Excel spreadsheet

The SAS 9 way is to output directly to Excel.

proc export data=mean_test1
          outfile='./mean_test1.xls'  /** ".xls" lets SAS know you want an Excel 5 spreadsheet **/
          replace;  /** if file already exists overwrite it ***/
run;

If no dbms engines are available for use:

proc export data=mean_test1
          outfile='./mean_test1.txt'  /** ".txt" lets SAS know you want an ASCII, tab delimited file **/
          replace;  /** if file already exists overwrite it ***/
run;

The old-fashioned way is to use put statements.

data _null_;  ** does not create a data set **;
 set mean_test1;
file './mean_test1.txt';  ** file to put the results.  ".txt " implies tab delimited ASCII text file **;
** first put the column headers ***;
if _n_=1 then put 'variable' <tab ' mean ';  ** you need to replace the text "<tab" with an actual tab **;
put  _name_ ' <tab ' mean ; ** you need to replace the text "<tab" with an actual tab **;
run;

Questions or comments? If you are affiliated to the Carolina Population Center, send them to Phil Bardsley; non-affiliates may contact the author Dan Blanchette.