topic Re: Trouble in importing data having some special characters in SAS Procedures

Trouble in importing data having some special characters

JoshuaHarris — Fri, 07 Sep 2018 06:50:53 GMT

Am importing an excel which contains some Latin characters as well. As it's not read properly, i googled and learnt that we can save the excel as unicode text file and then use proc import with encoding of utf-16.
Now am able to read it. But the dataset contains some extra symbols came along with latin characters.
Example
Â became Â-> or Âf like this.
How to read this properly and also how to process using string functions like cats and then report it back to excel properly with no extra symbols or letters.
Note: am using SAS for windows(9.3)
Thanks

Re: Trouble in importing data having some special characters

s_lassen — Fri, 07 Sep 2018 09:10:12 GMT

If you save your Excel file as Unicode text, you should also use unicode to read it into SAS:

Filename Textfile '<path>' ENCODING='UTF16B';
data want;
  infile textfile;
  input....

But I do not quite understand that only some characters get converted to two bytes - according to the SAS documentation, the UTF-16 encodings store all characters as two bytes. Are you quite sure that you are not using UTF-8?

Re: Trouble in importing data having some special characters

ballardw — Fri, 07 Sep 2018 16:29:06 GMT

FWIW I didn't find a UTF-16 save as option in my Excel 365, only UTF-8 and UTF-BigEndian

Re: Trouble in importing data having some special characters

JoshuaHarris — Fri, 07 Sep 2018 16:45:31 GMT

I saved it as unicode text and read the file again with utf-16.
Found a workaround for this temporarily.
Only few number of rows contains this special encoding data. So filtered
out using excel filter option on that column and copied it to notepad and
replace all there with space and copy back to excel solves my problem