ODS Excel / Proc Report - no wordwrap / merged cells?

Question

I use SAS ODS a lot to export prettied up excel files. One in particular has titles that go significantly longer than the width of all of the columns, so they word wrap, which makes the file look horrible.

Is there any way to export a file with word wrap turned off? I've searched on the SAS support forums, and have found very little helpful information.

%let myfile = "C:\Users\dicegods\testfile.xlsx"; 
ods excel file=&myfile. options (orientation='portrait' sheet_name='Baseball' embedded_titles='yes'); 

options LeftMargin=0.25in RightMargin=0.25in TopMargin=0.25in BottomMargin=0.25in; 
proc report data=sashelp.baseball; 
column name nHome nHits; 
define name / display 'Name'; 
define nHome / display 'Homers' sum; 
define nHits / analysis 'Hits' sum; 
title; title1 J=L "Baseball Players from SAS Help Database - sashelp library inEG - more run on stuff"; 
run;

can you also add a small snippet of code with which this issue could be reproduced? — Sendhilkumar Alalasundaram
%let myfile = "C:\Users\dicegods\testfile.xlsx"; ods excel file=&myfile. options (orientation='portrait' sheet_name='Baseball' embedded_titles='yes'); options LeftMargin=0.25in RightMargin=0.25in TopMargin=0.25in BottomMargin=0.25in; proc report data=sashelp.baseball; column name nHome nHits; define name / display 'Name'; define nHome / display 'Homers' sum; define nHits / analysis 'Hits' sum; title; title1 J=L "Baseball Players from SAS Help Database - sashelp library in EG - more run on stuff"; run; — dicegods
This is a simplified example, the real one is information that I'd rather not post here. Same issue though. Ideally, I'd like the titles to just flow across the page. I know I could increase the column widths of the pages, but really would rather not do that since it would make the data itself poorly formatted. — dicegods

Tom Tom · Accepted Answer · 2020-04-16T23:04:37

Try using ODS TEXT. The following code snippet shows a simple way to use ODS TEXT to insert a "title" into the top of the sheet being generated by the following PROC step. Look up the documentation on ODS TEXT to see how to control the formatting of the generated text

%let myfile = "C:\downloads\testfile.xlsx"; 
ods excel file=&myfile. options (orientation='portrait' sheet_name='Baseball' embedded_titles='no'); 
ods text="Baseball Players from SAS Help Database - sashelp library inEG - more run on stuff"; 

options LeftMargin=0.25in RightMargin=0.25in TopMargin=0.25in BottomMargin=0.25in; 
proc report data=sashelp.baseball; 
column name nHome nHits; 
define name / display 'Name'; 
define nHome / display 'Homers' sum; 
define nHits / analysis 'Hits' sum; 
title J=L "Baseball Players from SAS Help Database - sashelp library inEG - more run on stuff"; 
run; 
title;

ods excel close;

ODS Excel / Proc Report - no wordwrap / merged cells?

2 Answers