topic Re: Lines Wrapping in ODS Excel in SAS Programming

Lines Wrapping in ODS Excel

AshWynn87 — Wed, 23 Jan 2019 23:39:04 GMT

Hello there.

I'm attempting to export an excel file with 3 sheets. There is a dataset that applies to each sheet as you can see below. The problem is that I'm getting word wrapping in the data. It doesn't happen in all cells, but it does in most. I've tried flow='data' in the options but SAS doesn't recognize it. How can I get this random word wrapping to stop?

Re: Lines Wrapping in ODS Excel

ballardw — Wed, 23 Jan 2019 23:55:48 GMT

Did you try removing the absolute_column_width or using NONE instead of setting to 9.

When you try to put more text than might fit I suspect that word wrap gets turned on.

Re: Lines Wrapping in ODS Excel

ChrisNZ — Thu, 24 Jan 2019 02:26:20 GMT

Does your data contain carriage returns? Where does it come from? Do you need to clean it?

Re: Lines Wrapping in ODS Excel

AshWynn87 — Thu, 24 Jan 2019 14:08:44 GMT

I've already cleaned the data. There's no distinct reason it should be wrapping.

Re: Lines Wrapping in ODS Excel

AshWynn87 — Thu, 24 Jan 2019 14:10:01 GMT

I did try that to no avail.

Re: Lines Wrapping in ODS Excel

ballardw — Thu, 24 Jan 2019 16:25:18 GMT

@AshWynn87 wrote:

I've already cleaned the data. There's no distinct reason it should be wrapping.

Can you provide some example data that duplicates this behavior in the form of a data step? This is very likely somewhat data dependent and without data we are kind of flying blind.

Replace any sensitive values with characters strings of the same length or random numbers of a similar range of digits.

Test the example data to show that the behavior is similar.

Likely we only need 5 or 10 rows of data as examples.

If you need help creating data step of your data Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-data-AKA-generate/ta-p/258712 will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

Re: Lines Wrapping in ODS Excel

AshWynn87 — Thu, 24 Jan 2019 16:46:17 GMT

I ended up fixing it with an alternative method. Thanks everyone!

Re: Lines Wrapping in ODS Excel

ChrisNZ — Thu, 24 Jan 2019 20:25:42 GMT

@AshWynn87 Can you explain so others benefit from your findings?

Re: Lines Wrapping in ODS Excel

AshWynn87 — Thu, 24 Jan 2019 20:34:57 GMT

I used the same ods excel code in the initial post. I changed the code for assigning the variable by putting strip() around both vars included instead of around the whole thing.

Here's the code for assigning a variable, in this case varname RE_1:

Here's the corresponding output for said variable:

Re: Lines Wrapping in ODS Excel

ChrisNZ — Fri, 25 Jan 2019 01:43:38 GMT

It looks like your data wasn't so clean after all and you should have applied the function compbl().