Remove spaces type characters from string

[metallon]

Hello SAS Experts,

I get data via an XLSX import that contains the HEX symbol A0 or  , it looks like a space, but istn.

How can I remove this "fake" space?

So far this did not work:

proc sql;
CREATE TABLE _testtest AS
SELECT
TRANWRD (CN,' ',''),
COMPRESS (CN),
CN
FROM xxx;
QUIT;

Bye

John

[Reeza]

You're on the right track. Try the modifiers with the COMPRESS function, specifically S:

COMPRESS (CN, , 's'), 

s or S

adds space characters (blank, horizontal tab, vertical tab, carriage return, line feed, form feed, and NBSP ('A0'x, or 160 decimal ASCII) to the list of characters.

[metallon]

Hi Reeza,

I tried but it did not work:

proc sql;
CREATE TABLE testtest AS
SELECT
COMPRESS (CN, , 'S'),
CN
FROM xxx ;
QUIT;

I added a screen print. Tried with capital and non-cap S.

[Kurt_Bremser]

Create a new column with double the length of the original column and use the $HEXw. format to convert to hexadecimal representation, so you can see the offending character codes.

[metallon]

Hi Kurt,

Its A0 and tons of spaces 20.

I attached a new screen print.

proc sql;
CREATE TABLE test AS
SELECT
COMPRESS (CN, , 's'),
CN
  FORMAT=$HEX26.
FROM xxxx ;
QUIT;

[Kurt_Bremser · Accepted solution]

Then I'd try

compress(cn,'A0'x,'s')