topic Re: Compare two Strings in Hebrew in SAS Programming

Compare two Strings in Hebrew

AlexeyS — Thu, 30 Dec 2021 18:42:23 GMT

Hi,

I have to compare two variables that both of them contain values in Hebrew : Name and SecurityName. I my opinion Name variable is ok. Because when i create the values by myself and run compare function versus Name variable i get result value 0. The problem with SecurityName variable that comes from excel that i attached. Also i attached dataset.

When i use compare function, the result i got is not correct. Maybe the problem that i read file from xlsx file and proc import doesn't support encoding and in some forums i read that you cannot control the encoding of XLSX format file.

The first and last observations are identical, but the result is not correct.

When i tried to create the same data and values by myself and run the same compare function i got the right result, i.e maybe something in the encoding or in excel files i read.

I read xlsx files using proc import through call execute.

I tried kcompare also but the same results

result = compare(Name, SecurityName);

So what i should do?

Thank you

Re: Compare two Strings in Hebrew

LeonidBatkhan — Tue, 28 Dec 2021 23:40:44 GMT

Hi AlexeyS,

Could you please provide a sample of values and result you get. Also, could you verify that you are running SAS session with UTF-8 encoding?

Re: Compare two Strings in Hebrew

Ksharp — Wed, 29 Dec 2021 12:38:55 GMT

Did you try KCOMPARE() function ?

Re: Compare two Strings in Hebrew

AlexeyS — Wed, 29 Dec 2021 19:14:26 GMT

Hi,

The first and last observations are identical, but the result is not correct.

When i tried to create the same data and values by myself and run the same compare function i got the right result, i.e maybe something in the encoding or in excel files i read.

I read xlsx files using proc import through call execute.

I tried kcompare also but the same results

result0 = compare(Name, SecurityName);
result1 = compare(strip(Name), strip(SecurityName), 'ln');
result2 = compare(strip(Name), strip(SecurityName), 'nl');
result3 = compare(strip(Name), strip(SecurityName), 'n');
result4 = compare(strip(Name), strip(SecurityName));

When i run

proc options  option=encoding;
run;

I got : ENCODING=WHEBREW

So what i should do?

Thank you

Re: Compare two Strings in Hebrew

AlexeyS — Wed, 29 Dec 2021 19:15:33 GMT

yes i tried.

I uploaded some data and results when i run the code. Thank you

Re: Compare two Strings in Hebrew

AlexeyS — Wed, 29 Dec 2021 19:24:59 GMT

Hi,
The first and last observations are identical, but the result is not correct.
When i tried to create the same data and values by myself and run the same compare function i got the right result, i.e maybe something in the encoding or in excel files i read.
I read xlsx files using proc import through call execute.

I tried kcompare also but the same results
result0 = compare(Name, SecurityName);
result1 = compare(strip(Name), strip(SecurityName), 'ln');
result2 = compare(strip(Name), strip(SecurityName), 'nl');
result3 = compare(strip(Name), strip(SecurityName), 'n');
result4 = compare(strip(Name), strip(SecurityName));
When i run
proc options  option=encoding;
run;
I got : ENCODING=WHEBREW

So what i should do?
Thank you

Re: Compare two Strings in Hebrew

Patrick — Thu, 30 Dec 2021 12:00:45 GMT

Can you please provide your data via a text file (attachment) and not just a screen-shot? This would allow us to actually replicate and then resolve what you observe.

....actually: Provide the SAS table and ideally also the Excel

Re: Compare two Strings in Hebrew

AlexeyS — Thu, 30 Dec 2021 18:43:46 GMT

Yes of course. Please see original post, i added full information, dataset and excel. Thank you

Re: Compare two Strings in Hebrew

AlexeyS — Thu, 30 Dec 2021 18:44:22 GMT

Please see original post, i added full information, dataset and excel. Thank you

Re: Compare two Strings in Hebrew

Patrick — Fri, 31 Dec 2021 01:25:47 GMT

@AlexeyS wrote:

Please see original post, i added full information, dataset and excel. Thank you

@AlexeyS Thanks! That helped.

Add the following to your code and things will start working:

Securityname=kcompress(Securityname,'00'x);

For some reason your variable SecurityName is padded with hex 00 and though the strings in the two variables are actually always different.

data check;
  set test.test;
  l1=lengthn(name);
  l2=lengthn(Securityname);
  
  h1=put(name,hex72.);
  h2=put(Securityname,hex72.);
run;
proc print data=check;
run;

The Excel you've provided is not the source with which you've created the test SAS file. For this reason I couldn't further investigate what might have caused these trailing hex 00 values.

libname test xlsx '....';
data check;
  set test.test;
  l2=lengthn(Securityname);
  
  h2=put(Securityname,hex72.);
run;
proc print data=check;
run;

Re: Compare two Strings in Hebrew

AlexeyS — Fri, 31 Dec 2021 15:30:53 GMT

First of all Thank you very much.

I want to ask you a question, Is there any way to identify these problems in variable and which way?

Thank you

Re: Compare two Strings in Hebrew

Patrick — Sat, 01 Jan 2022 00:12:39 GMT

@AlexeyS wrote:

First of all Thank you very much.

I want to ask you a question, Is there any way to identify these problems in variable and which way?

Thank you

You could search for non-printable characters and then list the hex values if found any. Below some sample code.

libname test '...';
data check;
  set test.test;
  length _row_num 8 _varname $32 _non_print_chars $100 _hex_non_print_chars $200;
  array _cvars {*} _character_;
  do _i=1 to dim(_cvars);
  	_non_print_chars=kcompress(_cvars[_i],,'w');
    _len=klength(_non_print_chars);
	if _len>0 then
      do;
        _fmt=cats('hex',2*_len,'.');
		_hex_non_print_chars=putc(_non_print_chars,_fmt);
		_varname=vname(_cvars[_i]);
        _row_num=_n_;
		output;
	  end;
  end;
  keep _row_num _varname _hex_non_print_chars;
  rename _row_num=row_num _varname=varname _hex_non_print_chars=hex_non_print_chars;
run;
proc print data=check;
run;