topic ODS Excel- stop wrapping and adding quotes - Basically handling "find and replace new line in excel" in SAS Programming

ODS Excel- stop wrapping and adding quotes - Basically handling "find and replace new line in excel"

Stalk — Fri, 04 Aug 2023 16:29:33 GMT

When I am writing data to Excel using ODS Excel and Proc print..

Some of the data is wrapped to new line and automatic quotes are added to the data that is causing issues in the down stream analysis..

For ex:

"Social Awareness Analysis
Report"

"Final Percent Coverage of
Analysis structure"

How can I handle the below solution in SAS instead of each time opening the Excel and fixing thousands of records.

Google shows this solution...

To find and replace new lines in Excel, you need to¹ ² ³ ⁴:

Select all the cells where you want to find and replace new lines.
Press Ctrl+H to open the Find and Replace dialog box.
In the Find what field, enter Ctrl+J. This will represent a new line character.
In the Replace with field, enter the value that you want to replace new lines with, such as a comma, a space, or nothing.
Click Replace All to apply the changes.

Thank you

Re: ODS Excel- stop wrapping and adding quotes - Basically handling "find and replace new line

ballardw — Fri, 04 Aug 2023 17:11:09 GMT

You would have to provide example data and how you are sending the data to ODS Excel to demonstrate the quote issue. I haven't seen that unless the text value in the SAS data set has the quotes which would be fixable quite easy in a data step prior to the Print.

If you have values with comma's and transfer the data to a CSV file then the values should have quotes to keep the commas from being treated as delimiters.

You may have to provide more details on your Proc Print creating the ODS Excel output, such as the procedure and options. If you try to put 50 characters into a column defined width that only accepts 20 then the text should wrap for readability.

In general I would typically say that if other programs are supposed to read this data then a CSV or other text file format is likely to be more stable. You never know when some change in Excel is going to start reformatting your layout.

Re: ODS Excel- stop wrapping and adding quotes - Basically handling "find and replace new line

data_null__ — Fri, 04 Aug 2023 17:56:07 GMT

Better not to have the newline character in the first place.

FLOW=<"cell-names", "DATA", "HEADERS", "ROWHEADERS", "TABLES", "TEXT" >

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.

Re: ODS Excel- stop wrapping and adding quotes - Basically handling "find and replace new line

Stalk — Mon, 07 Aug 2023 13:23:35 GMT

Thank you so much. FLOW option worked.
options papersize=(22in 22in);
ods excel file= "C:\temp\gp.xlsx" options(flow="tables");