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

How to delete a string of special character in character variables

daisy6 — Mon, 30 Apr 2018 18:07:47 GMT

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

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

Tom — Mon, 30 Apr 2018 18:47:37 GMT

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.

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

Cynthia_sas — Mon, 30 Apr 2018 18:50:55 GMT

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

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

daisy6 — Mon, 30 Apr 2018 19:00:31 GMT

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

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

daisy6 — Mon, 30 Apr 2018 19:15:34 GMT

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.

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

daisy6 — Mon, 30 Apr 2018 19:25:03 GMT

Thank you very much. It works.

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

Ksharp — Tue, 01 May 2018 10:34:41 GMT

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