topic Re: How do I remove Tabs from data - Excel file is unreadable in New SAS User

How do I remove Tabs from data - Excel file is unreadable

AdamR — Wed, 03 Oct 2018 14:22:11 GMT

Hello,

We're having issues removing tabs from our data as after the data massage the export into an Excel file is Unreadable and it's mostly caused by users entering a tab character in the software.

We've tried the following and others without any success:

PROJECT_NOTES=compress(PROJECT_NOTES,' ',s);

In the quotes is a tab. Removing the tab or replacing it with a space is OK.

Thoughts?

Adam

SAS 9.3

Re: How do I remove Tabs from data - Excel file is unreadable

RW9 — Wed, 03 Oct 2018 14:32:05 GMT

To remove tabs I would say:

project_notes=compress(project_notes,'09'x);

Re: How do I remove Tabs from data - Excel file is unreadable

FreelanceReinh — Wed, 03 Oct 2018 15:36:30 GMT

@AdamR wrote:
We've tried the following and others without any success:

PROJECT_NOTES=compress(PROJECT_NOTES,' ',s);

In the quotes is a tab. Removing the tab or replacing it with a space is OK.

Hello @AdamR,

The modifier in the third argument of COMPRESS must be a character expression, i.e. 's' (unless s is a character variable containing the string 's' or 'S' etc.). With this correction the COMPRESS function should remove tabs ('09'x) and a few other "space characters" regardless what's in the quotes in the second argument.

Re: How do I remove Tabs from data - Excel file is unreadable

SuryaKiran — Wed, 03 Oct 2018 15:42:21 GMT

You can also use TRANWRD() function to replace tab with a space tranwrd(string,'09'x,' ')