Many ODS users do not like all the additional formatting that using the default style introduces in the following example. Without an ODS EXCEL option, how do you get clean output into Excel?

ODS HTML FILE='c:\temp\myfile.xls' RS=none;
 Proc print data=sashelp.class noobs;
 Run; 
ODS HTML CLOSE;

Note: The RS=NONE option is only needed on the mainframe. It's the default on all other operating systems, it is coded above because it ensures this example works on all platforms.

There IS a nice way to get very clean PROC output into Excel using ODS! Unwanted formatting can be fixed very nicely with what you learn about ODS templates. Styles such as MINIMAL can reduce some of the noise, but we can do better. This is an example specifying the minimal style:

ODS HTML FILE='c:\temp\myfile.xls' RS=none  STYLE=MINIMAL;
Proc print data=sashelp.class noobs; Run;
ODS HTML CLOSE;

The MINIMAL style still creates the output table with a RULE/GRID attributes which can be unwanted "noise". To remove the noise, you can take a copy of the minimal style using PROC TEMPLATE and use the RULES= attribute among others to make the default MINIMAL style even less noisy. The style "Table" in the Minimal style template is based on the "Output" style, and we can change this as follows:

style Output /
      rules = NONE
      frame = VOID
      cellpadding = 0
      cellspacing = 0
      borderwidth = 0;

Below is a complete working example that creates a new style called NOBORDER that overrides the "Table" style in the master "minimal" style that is stored in the master template sashelp.tmplmst. It has the above modifications and places those modifications into sasuser.templat and the ODS PATH by default reads SASUSER templates first. The example ensures that the master template is unaltered by issuing an ods path with read only access to the sashelp tmplmst master templates.

You can check that the new style is created in SASUSER by going to your RESULTS window, right mouse click on the folder "results" and then select TEMPLATES from the popup menu. Then navigate to sasuser.templat, then to the styles folder and NoBorder should be the new style in there.

/* set path to read only sashelp.tmplmst but update sasuser.templat */
ods path sasuser.templat(update)
         sashelp.tmplmst(read);

/* create new override style called noborder in sasuser.templat */
proc template;                                                         
  define style styles.noborder;                                            
     parent=styles.minimal;                                            
       style Table /                                                
         rules = NONE                                                    
         frame = VOID                                                    
         cellpadding = 0                                                 
         cellspacing = 0                                                 
         borderwidth = 0;                                                
   end;                                                                
run;                                                                   
/* apply the new noborder style to the ODS output */
ODS HTML FILE='c:\temp\myfile.xls'  STYLE=styles.noborder; 
title;
Proc print data=sashelp.class noobs; 
Run;
ODS HTML CLOSE; 

Using this program, the ODS output will appear as plain and unformatted for EXCEL purposes.

Brian Watts is a Senior Technical Specialist based in Sydney, Australia. He has worked for SAS for more than five years.

---

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

/* set path to read only sashelp.tmplmst but update sasuser.templat */
ods path sasuser.templat(update)
         sashelp.tmplmst(read);

/* create new override style called noborder in sasuser.templat */
proc template;                                                         
  define style styles.noborder;                                            
     parent=styles.minimal;                                            
       style Table /                                                
         rules = NONE                                                    
         frame = VOID                                                    
         cellpadding = 0                                                 
         cellspacing = 0                                                 
         borderwidth = 0;                                                
   end;                                                                
run;                                                                   
/* apply the new noborder style to the ODS output */
ODS HTML FILE='c:\temp\myfile.xls'  STYLE=styles.noborder; 
title;
Proc print data=sashelp.class noobs; 
Run;
ODS HTML CLOSE;