BookmarkSubscribeRSS Feed
LeeJenson
Calcite | Level 5
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
2 REPLIES 2
Cynthia_sas
SAS Super FREQ
Hi:
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:
http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia
[pre]
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}
style(data)={background=cxdddddd};
var height /style(data)={background=yellow};
var weight /style(data)={background=pink};
run;

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};
run;
ods _all_ close;
title;
ods listing;
[/pre]
LeeJenson
Calcite | Level 5
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............

Thank
Lee

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 949 views
  • 0 likes
  • 2 in conversation