- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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 to1234:
- 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
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
options papersize=(22in 22in);
ods excel file= "C:\temp\gp.xlsx" options(flow="tables");