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:
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:
flow="tables"
) but wrap is still active (I tried also by uncommenting tagattr="wrap:no"
but nothing happens)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?
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.