topic Re: Remove spaces type characters from string in SAS Programming

Remove spaces type characters from string

metallon — Thu, 19 May 2016 08:28:30 GMT

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

Re: Remove spaces type characters from string

Reeza — Thu, 19 May 2016 08:28:16 GMT

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.

Re: Remove spaces type characters from string

metallon — Thu, 19 May 2016 09:07:13 GMT

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.

Re: Remove spaces type characters from string

Kurt_Bremser — Thu, 19 May 2016 09:55:33 GMT

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.

Re: Remove spaces type characters from string

metallon — Thu, 19 May 2016 10:12:26 GMT

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;

Re: Remove spaces type characters from string

Kurt_Bremser — Thu, 19 May 2016 10:32:17 GMT

Then I'd try

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