SAS Programming

DATA Step, Macro, Functions and more
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-white.png

Our biggest data and AI event of the year.

Don’t miss the livestream kicking off May 7. It’s free. It’s easy. And it’s the best seat in the house.

Join us virtually with our complimentary SAS Innovate Digital Pass. Watch live or on-demand in multiple languages, with translations available to help you get the most out of every session.

 

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

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