topic ODS Excel involuntary concatenation in ODS and Base Reporting

ODS Excel involuntary concatenation

rlafond — Mon, 08 Jul 2019 15:20:03 GMT

Hello,

I'm using ODS Excel to output a data table and I've noticed that the output is being altered from the original by eliminating spaces.

Code:

ods excel file="\\hne_crms\hsra\RLafond\Quality\QIP\test3\&NAME._&LOB._QIP_Roster_&reportdate..xlsx";

ods excel options(sheet_name="&Name Roster &Month &Year");

PROC PRINT DATA = &NAME._&LOB._STEP2 NOOBS;
RUN;

ODS excel close;

Examples:

Source: VBC Group B

Output: VBC GroupB

Source: Adult BMI Assessment

Output: Adult BMIAssessment

Source: Appropriate Testing for Children with Pharyngitis

Output: Appropriate Testingfor Children withPharyngitis

It looks almost like some sort of formatting being done to replicate wrapping in Excel. Any idea why this is happening and how I can stop it? I've included an image showing what it looks like in Excel:

Re: ODS Excel involuntary concatenation

ballardw — Mon, 08 Jul 2019 15:18:05 GMT

You should include the code used to create the output including the ODS Excel statement to show options.

Since many procedures can provide output we kind of need to know which specific procedure and options were involved.

Also, you need to know that the message windows on this forum will also reformat text. So you need to be very careful what is posted.

Actual plain text such as code, log and raw data are best posted into code boxes opened with either the forums {I} or "running man" icons to prevent such reformatting as leading space removal, multiple space compression and some HTML code insertion/replacements.

Re: ODS Excel involuntary concatenation

rlafond — Mon, 08 Jul 2019 15:21:04 GMT

Updated original post with relevant code.

Re: ODS Excel involuntary concatenation

Reeza — Mon, 08 Jul 2019 16:08:45 GMT

Pretty sure you have a line return in there, which is recognized in an Excel cell, but will not be recognized in a tab name. I'm also fairly certain that this can't be done in a tab name manually either. You can however, replace the line return with a space instead and that will cause it to have the space correctly.

I think the character is "0A"x and you want to replace that with a single space instead. You can try using TRANWRD for that.

Re: ODS Excel involuntary concatenation

Cynthia_sas — Mon, 08 Jul 2019 21:08:16 GMT

Hi:

I find that if I make the cell wide enough that ODS EXCEL doesn't automatically wrap the line:

I believe the number of columns you have can also impact the wrapping behavior. But adjusting width is a place to start.

Cynthia

Re: ODS Excel involuntary concatenation

rlafond — Fri, 12 Jul 2019 12:13:01 GMT

So I ended up researching line returns and found that using the ODS Excel option

flow = 'tables'

Fixed the return issue.

Thanks for the direction!