DATA Step, Macro, Functions and more

How to delete a string of special character in character variables

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 102
Accepted Solution

How to delete a string of special character in character variables

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

 

 

 


Accepted Solutions
Solution
4 weeks ago
SAS Super FREQ
Posts: 9,263

Re: How to delete a string of special character in character variables

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

View solution in original post


All Replies
Super User
Super User
Posts: 7,860

Re: How to delete a string of special character in character variables

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.

Frequent Contributor
Posts: 102

Re: How to delete a string of special character in character variables

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

Solution
4 weeks ago
SAS Super FREQ
Posts: 9,263

Re: How to delete a string of special character in character variables

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

Re: How to delete a string of special character in character variables

Posted in reply to Cynthia_sas

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.

 

Frequent Contributor
Posts: 102

Re: How to delete a string of special character in character variables

Posted in reply to Cynthia_sas

Thank you very much. It works.

Super User
Posts: 10,626

Re: How to delete a string of special character in character variables

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

☑ This topic is solved.

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

Discussion stats
  • 6 replies
  • 131 views
  • 0 likes
  • 4 in conversation