BookmarkSubscribeRSS Feed
deleted_user
Not applicable
Hi Everyone,

I have a slew of reports being generated by SAS into Excel via ODS and the ExcelXP tagset. To cut down on post-processing, I've tried to have SAS generate the spreadsheets as close as possible to what the finished products need to look like by using various tagset options (column widths, headers, page centering, etc.).

The two biggest time-wasters in terms of post-processing involve specifically coloring clusters of cells according to what row/column they're in, along with adding in "spacing" columns and rows to create the cluster effect. Example:

Before:

3629 74.90% 3573 73.50%
1216 25.00% 1285 26.40%
4845 100.00% 4858 100.00%
3982 68.70% 3693 65.60%
1812 31.20% 1934 34.30%
5794 100.00% 5627 100.00%

After:

3629 74.90% (blank)3573 73.50%
1216 25.00% (blank)1285 26.40%
4845 100.00% (blank)4858 100.00%
(empty row)
3982 68.70% (blank) 3693 65.60%
1812 31.20% (blank) 1934 34.30%
5794 100.00% (blank) 5627 100.00%

Each of the above 4 clusters would have different BG colors, according to the values of the column and row headers.

I've tried to write Excel macros to do this portion, but they're not universal enough. Is there any way to achieve either of these tasks through SAS, or am I out of luck?
3 REPLIES 3
Chevell_sas
SAS Employee
Using PROC REPORT with the CALL DEFINE statement, you can color code the background depending on the row number. To do this, you can create a counter variable within the compute block and conditionally execute the CALL DEFINE statement.

The ExcelXP tagset would have to get the spacing from the procedure, so the tagset is limited to what it can do by the procedure. PROC REPORT can generate lines at certain places in the table with the compute block and the line statement. This might/might not help in your situation. The below example will color code the background of certain rows based on the counter variable created. It will also add a blank line when the value of the variable age changes.


ods tagsets.excelxp file="temp1.xls";

proc report data=sashelp.class nowd;
column age sex height weight;
define age / order;
define sex / order;
define height / analysis;

compute age;
count+1;
if count in(6,7,8) then call define(_row_,"style","style={background=red}");
else if count in(16,17,18) then call define(_row_,"style","style={background=blue}");
endcomp;

compute before age;
line " ";
endcomp;
run;

ods tagsets.excelxp close;
deleted_user
Not applicable
This solution works for any row-wise items (i.e. the breaks between rows), but I can't think of any ways to get a break between columns. Likewise, the color technique works, but I have to color according to the column variable value, as well as the row value.

If I can at least get column breaks and the only thing I have to worry about is the color, that still makes my life a bit easier! (The row breaks are certainly helpful as well, though.)
Cynthia_sas
SAS Super FREQ
Elayne:
One way to insert a "placeholder" column is to use a compute block to set the value for a computed column that is just 1 space. This is shown below in a modification to Chevell's example using variables bl1 and bl2. Once you have introduced the placeholder or blank columns into the output, you could use the ExcelXP method for changing the column width.
As for your requirement to test a column value, consider another modification to Chevell's code, shown below. Note how the background value for the height or weight column is set using the _col_ attribute. There are a lot of different ways you could do this kind of highlighting. It is possible to set the value of a column based on the value of another column...but since proc report builds the report row from left to right, you may have to experiment a bit with the logic of your compute blocks to get things to highlight correctly.
cynthia

[pre]
*** sas code;
ods listing close;
ods tagsets.excelxp file="c:\temp\temp1.xls";

proc report data=sashelp.class nowd;
column age bl1 sex bl2 height weight;
define age / order;
define bl1 / computed ' ';
define sex / order;
define height / analysis mean "Avg Ht";
define bl2 / computed ' ';
define weight / analysis mean "Avg Wt";
compute bl1 / character length=1;
bl1 = ' ';
endcomp;
compute bl2 / character length=1;
bl2 = ' ';
endcomp;
compute age;
count+1;
if count in(6,7,8) then do;
call define(_row_,"style",
"style={background=red}");
end;
else if count in(16,17,18) then do;
call define(_row_,"style",
"style={background=blue}");
end;
endcomp;
compute height;
if height.mean = 59 then
call define(_col_,"style",
"style={background=pink}");
endcomp;
compute weight;
if weight.mean le 99 then
call define(_col_,"style",
"style={background=cyan}");
else if weight.mean gt 110 then
call define(_col_,"style",
"style={background=beige}");
endcomp;
compute before age;
line " ";
endcomp;
run;

ods tagsets.excelxp close;
[/pre]

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
  • 3 replies
  • 1884 views
  • 0 likes
  • 3 in conversation