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:
Thank you
Better not to have the newline character in the first place.
specifies that a designated Worksheet area enables Wrap Text and disables newline character insertion. Excel wraps the text to the column width.
enables Wrap Text for a single cell such as “A12” or a cell range such as “C1:E4”.
enables Wrap Text for table data cells.
enables Wrap Text in table column headers.
Alias | HEADER |
enables Wrap Text for table row headers.
Alias | ROWHEADER |
enables Wrap Text for all parts of a table: HEADER, ROWHEADER, and DATA.
Alias | TABLE |
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.
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.
Better not to have the newline character in the first place.
specifies that a designated Worksheet area enables Wrap Text and disables newline character insertion. Excel wraps the text to the column width.
enables Wrap Text for a single cell such as “A12” or a cell range such as “C1:E4”.
enables Wrap Text for table data cells.
enables Wrap Text in table column headers.
Alias | HEADER |
enables Wrap Text for table row headers.
Alias | ROWHEADER |
enables Wrap Text for all parts of a table: HEADER, ROWHEADER, and DATA.
Alias | TABLE |
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.
Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.
If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.