topic Re: How do I constrain row height when defining column width in proc report? in ODS and Base Reporting

How do I constrain row height when defining column width in proc report?

Ryanb2 — Mon, 06 May 2024 16:59:47 GMT

I need to define column width to standardize the look of the report. However, when I set the column width it automatically expands the row height for each cell. I tried setting the cellheight in various places in the code but I can't seem to constrain it. How is this done?

Here's an example using the sashelp.cars dataset that gives me the same results.

ODS results off;

ODS listing close;

ODS TAGSETS.EXCELXP

file="C:\test.xml"

STYLE=Printer

OPTIONS (

Sheet_Name = "NEW"

Orientation = 'landscape'

FitToPage = 'no'

Pages_FitWidth = '1'

Pages_FitHeight = '100'

embedded_titles = 'yes'

);

PROC REPORT DATA=sashelp.cars

style(header)=[fontfamily=helvetica fontsize=8pt textalign=l]

style(column)=[fontfamily=helvetica fontsize=8pt textalign=l TAGATTR='format:text'];

columns make model type origin msrp drivetrain horsepower mpg;

DEFINE make / STYLE(column)={width=2cm};

DEFINE model / STYLE(column)={width=2cm};

DEFINE type / STYLE(column)={width=2cm};

DEFINE origin / STYLE(column)={width=2cm};

DEFINE msrp / STYLE(column)={width=15cm};

DEFINE drivetrain / STYLE(column)={width=2cm};

DEFINE horsepower / STYLE(column)={width=2.5cm};

DEFINE mpg / STYLE(column)={width=2cm};

RUN;

ods tagsets.excelxp close;

Re: How do I constrain row height when defining column width in proc report?

ballardw — Mon, 06 May 2024 18:51:54 GMT

@Ryanb2 wrote:

I need to define column width to standardize the look of the report. However, when I set the column width it automatically expands the row height for each cell. I

Are you talking about values such as Model that have long text such as "3.5 RL w/Navigation 4dr" that wrap text to another line because it is too long to fit in space provided?

If so, what do you want to happen? Cutoff the extra text? Not wrap the text?

Note: SASHELP.CARS has MPG_City and MPG_Highway but not "MPG" as variables.

Re: How do I constrain row height when defining column width in proc report?

Ryanb2 — Mon, 06 May 2024 19:30:16 GMT

Sorry. It looks like when I adapted my code to the cars dataset I didn't do it perfectly. Just trying to find a dataset users can run that creates wide column heights.

It's fine with me if the text is truncated in places. I can refine the widths if needed. If there's a way to make it wrap and expand dynamically without the egregious extra space then all the better.

Re: How do I constrain row height when defining column width in proc report?

ballardw — Mon, 06 May 2024 20:22:41 GMT

I'm afraid that you will have to more clearly describe, or provide a better data set, that demonstrates your specific concern.

Your comment "egregious extra space" without a concrete example isn't clear enough to code.

The ExcelXP (or other formats) generally will attempt to "dynamically fit" text into provide width columns by wrapping text too long to fit in a defined report column width. XML files may have an issue with which specific file viewer you use though. If you haven't looked at the generated XML in a text viewer you should to see just how much default stuff is set that the viewer gets to interpret.

Any particular reason you are using ODS ExcelXP for output?

Re: How do I constrain row height when defining column width in proc report?

Ksharp — Tue, 07 May 2024 02:07:26 GMT

You could use option "Row_Heights=".

ODS TAGSETS.EXCELXP
file="C:\temp\test.xls"
STYLE=Printer
OPTIONS ( 
Sheet_Name = "NEW"
Orientation = 'landscape'
FitToPage = 'no'
Pages_FitWidth = '1'
Pages_FitHeight = '100' 
embedded_titles = 'yes'

Row_Heights='0,40,0,0,0,0,0'

)   ;   
PROC REPORT DATA=sashelp.cars nowd
style(header)=[fontfamily=helvetica fontsize=8pt textalign=l] 
style(column)=[fontfamily=helvetica fontsize=8pt textalign=l TAGATTR='format:text']; 
columns make model type origin msrp drivetrain horsepower mpg;
 
DEFINE mpg / STYLE(column)={cellwidth=10cm};

RUN;
ods tagsets.excelxp close;