Remove spaces type characters from string

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 133
Accepted Solution

Remove spaces type characters from string

[ Edited ]

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


Accepted Solutions
Solution
‎05-19-2016 06:58 AM
Esteemed Advisor
Posts: 6,646

Re: Remove spaces type characters from string

Then I'd try

compress(cn,'A0'x,'s')
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Grand Advisor
Posts: 17,327

Re: Remove spaces type characters from string

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.
Frequent Contributor
Posts: 133

Re: Remove spaces type characters from string

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.

 

 

Attachment
Esteemed Advisor
Posts: 6,646

Re: Remove spaces type characters from string

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 133

Re: Remove spaces type characters from string

[ Edited ]

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;

Attachment
Solution
‎05-19-2016 06:58 AM
Esteemed Advisor
Posts: 6,646

Re: Remove spaces type characters from string

Then I'd try

compress(cn,'A0'x,'s')
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 470 views
  • 3 likes
  • 3 in conversation