TRANSLATE() function became /... crazy :-( .

[MBKsk]

Hi,

I'm fighting with a translate() function in my new job.

I have my 'old faithful' code fragment to remove diacritics (accented characters) which worked fine (both: in datastep and proc sql) -till current job:

TRANSLATE(FirstNAME, "aaccdeeillnnoorrsstuyzz", "áäčćďéěíĺľňńóôŕřšśťúýžź")

 + I tried to replace " with apostrophs >>> no!

 + I tried to change obly one character (e.g. á to a) - it worked well

   + but when I extend <StringFROM> and <StringTO> to very small set of characters (fom 'áíšžň' to 'aiszn') >> translate() started to mix the characters: ..

      + Adamík  =>>> Adami k   ... error: add space after correct change
      + Arpáš =>>> Arpi �       ... change á to i instead of a + strange char inst. "s"

      + Badáň =>>> Badi ň    ... same "a" + ignore ň from list

      + Ažimov =>>> Ažimov   ... ignore ž 

Bonus: I have another 'shape' to remove strange chars from a name and it seems works 😄 ... (all are changed to a space which is further celaning by COMPBL() 😞

COMPBL(translate(FirstNAME, "                                 ", "0123456789/\:;|{}[]()!@#$%^&*_.,-")) as MyLoveNAME,

Have somebody an idea where is it screwed?

PS: I hacked it by this ugly patch ...

tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(tranwrd(
   lowcase(cli.FirstNAME),
       'á', 'a'), 'ä', 'a'), 'č', 'c'), 'ď', 'd'), 'é', 'e'), 'ě', 'e'), 'í', 'i'), 'ĺ', 'l'), 'ľ', 'l'), 'ň', 'n'), 'ó', 'o'), 'ô', 'o'), 'ŕ', 'r'), 'ř', 'r'), 'š', 's'), 'ť', 't'), 'ú', 'u'), 'ý', 'y'), 'ž', 'z') as NameNoDIA,

    ...but it makes me sick 😞 and -truly: i'm surprising it works.

-thx- Martin

[PaigeMiller · Accepted solution]

Convert characters with diacritical marks to their equivalent without the diacritical mark via the BASECHAR function

Example:

data fake;
     text='ÇñaıŁá';
     text2=basechar(text);
run;

[MBKsk]

Thanks, it works. I didn't know this function exists... of course there are so much functions in the SAS that somebody can't embrance most of them (especially when is focussing to analytics and mining -e.g.). MBK

[FreelanceReinh]

Hello @MBKsk,

Glad to see that Paige Miller's solution worked for you. Then it would be fair and help later readers if you marked his helpful reply as the accepted solution, not your own "thank you" post. Could you please change that? It's very easy: Select his post as the solution after clicking "Not the Solution" in the option menu (see icon below) of the current solution.

[ballardw]

Check the encoding of the file.

Some of this sounds like a file that previously used high order ASCII characters, those with numeric representation over 127 to hold the diacritics but now may have UNICODE characters, which are different.

The "add a space" is actually a clue as ASCII characters take one-byte for storage but UNICODE starts at two bytes and may take as many as 4 in some languages.

[MBKsk]

Thanks to describe why it needn't works anytime (which I a little bit understand at least 🙂 )

...but - next it a little overolad my weight category

   ...I've tried to see the coding of my source tab (proc contents) but I realized "default" as result :-'

   ...and I didn't suceed to find a structure of the UNICODE character (for example this á with space as bonus)

Thank you - it is interesting to know some background.

[Tom]

TRANSLATE() works on single bytes.  If you are using ENCODING=UTF-8 then some of the "characters" in your string will be multiple bytes long.  That is going to cause all kinds of crazy to happen.

Consider just two of those characters.  Let's make a little test, Let's but the FROM and TO strings into their own variables so we can get a look at what they contain.

 73         data test;
 74           String =  "XáäčY";
 75           To = "aa" ;
 76           From = "áä" ;
 77           Want = String;
 78           Want = TRANSLATE(string,to,from);
 79           put (string -- want) (=$quote.);
 80           put (string -- want) (=$hex.);
 81         run;
 
 String="XáäčY" To="aa" From="áä" Want="Xaaa čY"
 String=58C3A1C3A4C48D59 To=6161 From=C3A1C3A4 Want=5861616120C48D59
 NOTE: The data set WORK.TEST has 1 observations and 4 variables.

Notice that the FROM string has 4 bytes and the TO string only has 2 bytes.  TRANSLATE() will pad the TO string with spaces ('20'x) to make them the same length.  So you are telling TRANSLATE to perform the following replacements:

To=6161 From=C3A1C3A4
C3 -> 61
A1 -> 61
C3 -> 20
A4 -> 20

Notice that you gave conflicting instructions on how to translate the 'C3'x bytes.  First said make it an a and then you said make it a space.

Let's look at the result and see which one it decided to map that byte to.

String=58C3A1C3A4C48D59 
Want  =5861616120C48D59

So C3 was mapped to 61 (the letter a) and  A1 was also mapped to the letter a.

And A4 was mapped to a space.

So TRANSLATE() uses the FIRST value you ask it to translate into when you have the same byte multiple times in the FROM list of bytes.

If you want to translate characters instead of bytes then use the KTRANSLATE() function.

KTRANSLATE(FirstNAME, "aaccdeeillnnoorrsstuyzz", "áäčćďéěíĺľňńóôŕřšśťúýžź")

If we use the same test program with KTRANSLATE() instead this is the result:

 String="XáäčY" To="aa" From="áä" Want="XaačY"
 String=58C3A1C3A4C48D59 To=6161 From=C3A1C3A4 Want=586161C48D592020

Notice the two extra spaces on the end of WANT.  That is because WANT was defined long enough to store STRING.  And after replacing two characters that used 2 bytes each with a character that needs only one byte the resulting string is 2 bytes shorter.