BookmarkSubscribeRSS Feed
☑ This topic is solved. Need further help from the community? Please sign in and ask a new question.
Stalk
Pyrite | Level 9

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:

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

 Thank you

1 ACCEPTED SOLUTION

Accepted Solutions
data_null__
Jade | Level 19

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.

View solution in original post

3 REPLIES 3
ballardw
Super User

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.

data_null__
Jade | Level 19

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.

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

SAS INNOVATE 2024

Innovate_SAS_Blue.png

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. 

Register now!

How to Concatenate Values

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.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 265 views
  • 0 likes
  • 3 in conversation