How to delete a string of special character in character variables

[daisy6]

Hello SAS guys,

When I import the excel file into SAS, the SAS dataset has some special charater string beginning with &#, which the excel file don't have, such as " " ,"&# 8875;", "’".  How can I delete those unwanted strings. It seems there is nothing strange with excel file, however these special string shows up between the space of two words instead of blank space. Any idea is appreciated.

my SAS data is :

var1

Newton’s seconde law for particles

Newtow’s first law for particales

Region 1

13. Basic Math

4.Instruction and#&8875; Concepts

7.Vibratons#&8875;knowledge

[Tom]

What method did you use to import the data?  What version of SAS are you using.

I think this issue of some unicode characters being stored this way was fixed in a recent update.

You can just use TRANWRD() function to convert them.

160 (or 'A0'x) is a "non-breaking" space.  So just replace it with a space.

8217 is a curly single quote. I would just replace it with a normal single quote.

[daisy6]

Hi Tom,

I use SAS 9.4(TS1M3). The import code is proc import datafile="datadir\excelfilename.xlse" out=have dbms=xlsx replace;

getnames=no;

run;

Thanks for answering the question

[Cynthia_sas · Accepted solution]

Hi:
You have to be careful how you translate them. For example, on this site:
https://www.w3schools.com/charsets/ref_utf_punctuation.asp
you can see that &#8217 is the single apostrophe or RIGHT SINGLE QUOTATION MARK you need for Newton's.

And the &#160, shown here: https://www.w3schools.com/charsets/ref_html_8859.asp seems to be the non-breaking space.

And the #8875 seems to be some kind of math symbol, as shown here: https://www.w3schools.com/charsets/ref_utf_math.asp.

That form &#xxxx; is an HTML entity and you can look them up on the W3C site.

You might be able to have Excel use the correct entity when it displays the value if you change the font for the sheet to a Unicode font.

Cynthia

[daisy6]

Thank you Cynthia_sas. How can I convert the excel file to a unicode font? The format cells option in excel do not provide unicode choice.

[daisy6]

Thank you very much. It works.

[Ksharp]

data _null_;
x='Newton’s seconde law for particles';
x=htmldecode(x);
put x=;
run;