Achieve the default Excel style with proc template and ods excel

Rabelais — Wed, 31 Jul 2024 16:27:39 GMT

Hi, I'm trying to replicate the default plain style in Excel by using proc template and ods excel.

Let's see an example to understand what I'd like to generate using SAS.

As you can see from the image below, it is the default style when manually creating an excel file. Basically, this style can be summarized by the following features:

transparent background color
black text color
Calibri font with 11pt size and left alignment
20 px row height and 64 pixels column width
no wrap text, i.e. the text inside cells must not be wrapped into multiple lines

This is what I obtain with the code below

data test;
test=123;
description="lorem ipsum dolor sit amet consectetur adipiscing elit sed do eiusmod tempor incididunt";
status="pass";
output;
run;

proc template;
define style styles.excel_update;
parent=styles.excel;
class body, header, data / fontfamily=Calibri fontsize=11pt just=left color=black fontweight=medium
	backgroundcolor=_undef_ nobreakspace=on asis=off cellpadding=0 cellspacing=0 height=20 CELLWIDTH=64
/*	tagattr="wrap:no"*/
;
end;
run;

ods excel
	file="your_path\test.xlsx"
	style=styles.excel_update 
/*	options(*/
/*		flow="tables"*/
/*		absolute_Column_Width="64px"*/
/*		absolute_row_height="20px"*/
/*	)*/
;

proc report data=test;
	columns _ALL_;
run;

ods excel close;

As you can see, features 4 and 5 are not achieved. In particular, notice that the colums width is 54 pixels instead of 64, and the row height of the rows below the table is 18 pixels instead of 20. Moreover, the text not only is wrapped into multiple lines, but line breaks are added after each word.

By uncommenting the lines

/*	options(*/
/*		flow="tables"*/
/*		absolute_Column_Width="64px"*/
/*		absolute_row_height="20px"*/
/*	)*/

I obtain the following result, which is very good, but has minor flaws:

the row height of the rows below the table is 18 pixels instead of 20
line breaks are not added to text (thanks to flow="tables") but wrap is still active (I tried also by uncommenting tagattr="wrap:no" but nothing happens)
the number 123 is displayed at the center of the cell, this is because what the cell actually contains is " 123" instead of the number 123 (EDIT: this is caused by height=20 CELLWIDTH=64)

Question 1: why nobreakspace=on asis=off don't work? In the documentation I read that ASIS=OFF trims leading spaces and ignores line breaks, and that NOBREAKSPACE=ON does not let SAS break a line at a space character. So why in the second screenshot we see that lines are broken and that line breaks are not ignored?

Question 2: is it possible to achieve the desired result without using ods excel options, but just by using proc template style attributes?

Question 3: if the answer to Question 2 is "no", why do the three flaws described above happen and how to solve them?

topic Achieve the default Excel style with proc template and ods excel in ODS and Base Reporting

Achieve the default Excel style with proc template and ods excel