topic Re: ODS excel not wrapping text in spreadsheet in SAS Programming

ODS excel not wrapping text in spreadsheet

SASUserRocks — Tue, 04 Apr 2023 10:52:22 GMT

Dear Friends,

Can you please help me with this query.

Using below code to generate excel from SAS dataset. Porc print is wrapping fine , however excel output has unwrapped text. I want some clean output ,unwrapped text in spreadsheet.

ods excel file="/FFLDatacleansing/ABC- &StartWeek.-&EndWeek. 2023.xlsx" options(sheet_interval='BYGROUP'
sheet_name='#byval1') style=meadow
options (tab_color ="Grey" ) ;

proc print data=FINAL_OUTPUT1 noobs style(header)={backgroundcolor=Maroon color=white} style(data)={tagattr="wrap:no" asis=on } ;
by Group;
variables ID Customer_CID Customer_Name Service_Type Service_Sub_Type Queue_Name
'Staff ID'n 'Staff Name'n Svs_Failure RM_Code RM_Name 'Manager Name'n Lead
Team Group Creator_Business 'Close Date'n Contact_1
Contact_1_Mob Contact_1_Email / style(data)= {tagattr="type:num"} ;
run;

ods excel close;
GOPTIONS ACCESSIBLE;
filename outbox email 'SAS_No_REPLY@adcb.com';

Re: ODS excel not wrapping text in spreadsheet

FloydNevseta — Tue, 04 Apr 2023 18:38:35 GMT

Explore the flow= option of the ODS EXCEL statement.

specifies that a designated Worksheet area enables Wrap Text and disables newline character insertion. Excel wraps the text to the column width.

cell-names

enables Wrap Text for a single cell such as “A12” or a cell range such as “C1:E4”.

DATA

enables Wrap Text for table data cells.

HEADERS

enables Wrap Text in table column headers.

Alias

HEADER

ROWHEADERS

enables Wrap Text for table row headers.

Alias

ROWHEADER

TABLES

enables Wrap Text for all parts of a table: HEADER, ROWHEADER, and DATA.

Alias

TABLE

TEXT

makes ODS TEXT output work like titles, footnotes, PROC titles, and BY lines. The text is written into multiple merged cells and Wrap Text is enabled.

NONE

specifies that text wrapping is not enabled. This option can turn off the FLOW= option.

DefaultExamples

NONE

 ods excel file="c:\file-path\test.xlsx" options(flow="A2");

ods excel file="c:\file-path\test.xlsx" options(flow="tables");

ods excel file="c:\file-path\test.xlsx" options(flow="text");

Re: ODS excel not wrapping text in spreadsheet

SASUserRocks — Wed, 05 Apr 2023 05:16:40 GMT

I dont want text to be wrapped. Just unwrapped data in all cells. How to achieve that.

Re: ODS excel not wrapping text in spreadsheet

andreas_lds — Wed, 05 Apr 2023 05:54:23 GMT

From the docs posted by @FloydNevseta i would assume that flow=none does this.