The SAS Output Delivery System and reporting techniques

Excel formatting

Posts: 33

Excel formatting

I am looking for some simple examples on how to manipulate excel sheet formatting. How can I adjust the background colour for a column of data? Message was edited by: Tim2010
Posts: 8,743

Re: Excel formatting

Partly, it depends on what your procedure of choice is. For example, if you were using PROC PRINT, then you can use STYLE= overrides to change the background color of a particular column. PROC REPORT and PROC TABULATE have slightly different syntax. (Also, if you were using other procedures, like PROC GLM or PROC MEANS, then you'd need to use other, more advanced techniques to change the background color for a column of data.)

See the program below for an example of changing background colors based on using PROC PRINT and SASHELP.CLASS.

Also useful to know would be HOW you are going to get your output into Excel? Are you using PROC EXPORT or the Libname Engine?? If so, then, since data sets do not have color specifications, there would be no color. If you are using ODS CSV techniques, again, no color here. But if you were using ODS HTML techniques or ODS TAGSETS.EXCELXP, then you would be able to get background color to change (as shown in the program below). ODS MSOFFICE2K is an HTML-based tagset destination - -which creates an HTML file that can be opened in Excel; ODS TAGSETS.EXCELXP creates an XML file (Spreadsheet Markup Language XML for Office 2003) that created an XML file which can be opened in Excel. Note the difference between these 2 outputs and the CSV file. Particularly, note how the 2 different procedures created 2 different worksheets using the ExcelXP destination, but only 1 worksheet using the other 2 destinations.

For more information about sheet formatting, in particular using TAGSETS.EXCELXP, see this paper:

ods listing close;
** note: naming the files with .XLS extension ONLY to fool registry;
** and launch Excel on double click.;
** Files created are ASCII text files, not true, binary Excel files.;
** Look at files with Notepad to see underlying ASCII text data and markup.;

ods csvall file='c:\temp\comma.csv';
ods msoffice2k file='c:\temp\mso_ht.xls' style=sasweb;
ods tagsets.excelxp file='c:\temp\xp_xml.xls' style=sasweb;

proc print data=sashelp.class;
title 'Only Female Students';
where sex = 'F';
var name sex age / style(header)={background=purple}
var height /style(data)={background=yellow};
var weight /style(data)={background=pink};

proc print data=sashelp.class;
title 'Students who are 14 or older';
where age ge 14;
var name sex / style(data)=RowHeader;
var age / style(data)={background=cxdddddd};
var height /style(data)={background=yellow};
var weight /style(data)={background=cxdddddd};
ods _all_ close;
ods listing;
Posts: 33

Re: Excel formatting

Hello Cynthia,

Thanks again for taking the time with your detailed reply.

The method I am using is a PROC EXPORT and I am not using any ODS type commands as they are new to me. The method is up to me as long as the end result is cool :-)

Seems like this is where I am going wrong.

I shall start to enter the ODS world... here goes............

Ask a Question
Discussion stats
  • 2 replies
  • 2 in conversation