BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
metallon
Pyrite | Level 9

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

1 ACCEPTED SOLUTION
5 REPLIES 5
Reeza
Super User

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
Pyrite | Level 9

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.

 

 


cn.png
Kurt_Bremser
Super User

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
Pyrite | Level 9

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;


hex2.png

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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