topic Re: String separated by return instead of space when exporting to Excel in New SAS User

String separated by return instead of space when exporting to Excel

polpel — Wed, 02 Oct 2019 12:30:36 GMT

Hello,

I've come accross a very annoying problem when exporting a table to Excel using ODS.

Basically I have a column where two strings are longer than the rest of the strings, and contain two words separated by a space ("Current Medicine" and "Consultations Visits").

When I export to Excel, these to variables are no longer separate by a space but by a return so I get:

Current

Medicine

Consultations

Visits

These are still in the same cell but it's still annoying because the stuff in which I use these tables in Excel needs it to be on the same line.

I hope this was clear, it wasn't very easy to explain...

Thanks in advance,

Regards

Re: String separated by return instead of space when exporting to Excel

andreas_lds — Wed, 02 Oct 2019 12:55:14 GMT

Posting data and the code you used to export the file will help us finding a solution.

Re: String separated by return instead of space when exporting to Excel

Tom — Wed, 02 Oct 2019 13:31:21 GMT

ODS is trying to make your print out look pretty. It is a printing utility and not a data transfer utility.

You have two options.

First is to switch to using LIBNAME or PROC EXPORT to generate your Excel file as a straight data transfer.

Second is to search for the option they added to ODS EXCEL that lets you tell it that you don't want it to insert line breaks into long strings. Once you find that option make sure you are using a recent enough version of SAS that it has been implemented in your version.

Re: String separated by return instead of space when exporting to Excel

Ksharp — Thu, 03 Oct 2019 13:48:35 GMT

Try some style.

ods excel ...

proc report ....

.........

define xx / style={ tagattr="wrap:no"} ;

..........