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

[AdamR]

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

[RW9]

To remove tabs I would say:

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

[FreelanceReinh]

---

@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.

[SuryaKiran · Accepted solution]

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