topic Re: Identify unseen characters and resolve in SAS dataset for a particular column in SAS Programming

Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Mon, 22 Mar 2021 10:14:26 GMT

Hi all,

Could you please help me - How to identify unseen characters in SAS dataset under one particular field. data looks normal to my eyes in SAS table but actually there are some unseen control characters which i can not see. can you please guide how can i recognise those records

Re: Identify unseen characters and resolve in SAS dataset for a particular column

FreelanceReinh — Mon, 22 Mar 2021 12:09:53 GMT

Hi @JJP1,

If your character data use a single-byte character set (SBCS), try this:

data want;
set have;
_pos=findc(var,' ','kg');
if _pos;
_c=rank(char(var,_pos));
run;

Variable _pos will contain the position of the first invisible character, except ordinary blanks, in character variable var. If such a character is found, the observation will be written to dataset WANT and variable _c will contain the decimal ASCII code (assuming an ASCII system) of that invisible character (e.g., 9 for a tab character, 160 for a non-breakable space).

Edit: To check all characters of var in the selected observations at once, apply the $HEXw. format to var.

Example:

proc print data=want(obs=10);
format var $hex80.;
run;

Adapt the length of the format (here: 80) to twice the length of var. The output will display the hexadecimal ASCII codes of the characters, e.g., A0 for a non-breakable space.

To remove the unwanted characters, use the COMPRESS function.

Example:

data clean;
set have;
var=compress(var,' ','kg');
run;

But maybe you rather want to replace them, e.g., by using the TRANSLATE function.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 07:08:23 GMT

Thnaks @FreelanceReinh . please find below sample screen shot output and i am trying to run the proc print to see unseen characters but i am getting error as var is not character datatype. and there is no data for var field. please help

Re: Identify unseen characters and resolve in SAS dataset for a particular column

FreelanceReinh — Tue, 23 Mar 2021 08:14:53 GMT

In your initial post you wrote about "one particular field" being affected by invisible characters -- without disclosing the variable name. Therefore I used the generic variable name var for that "particular field." Apparently I should have mentioned that you need to replace this name by the name of your real character variable, as SAS doesn't know by default where to look for the invisible characters. Sorry for this omission.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

japelin — Tue, 23 Mar 2021 08:52:04 GMT

try use anycntrl.

data want;
  set have;
  if ANYCNTRL(VAR);/*Replace VAR with one particular field name which has unseen characters */
run;

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 10:10:24 GMT

hi @japelin . i have ran the query as suggested by you. but i have no data in output table want.

does this mean that there is no unseen characters ? but there are unseen characters please

Re: Identify unseen characters and resolve in SAS dataset for a particular column

japelin — Tue, 23 Mar 2021 10:33:13 GMT

try this code.

if your data has a control character, uns= shows ASCII collating sequence of it.

 data want;
   set have;
   pos=ANYCNTRL(VAR);/* replace var to your field name */
   if pos>0 then uns=rank(substr(var,pos,1));
   put uns=;
run;

For example, the tab is 9.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 10:53:28 GMT

yes sorry i got the output data now. iam just trying to see the unseen characters by copying to notepad++ but not able to see those unseen values.

can't we not see those unseen characters ?

am i wrong please ?

Re: Identify unseen characters and resolve in SAS dataset for a particular column

FreelanceReinh — Tue, 23 Mar 2021 11:10:42 GMT

What kind of characters did you find with SAS, i.e., their decimal code in variable _c or the hexadecimal code seen with the $HEXw. format? Then why do you need to use external tools in addition?

Re: Identify unseen characters and resolve in SAS dataset for a particular column

japelin — Tue, 23 Mar 2021 11:29:55 GMT

Upload the result of the output of the uns variable to the log, just as an image.

like this.

Since the control code is not visible, you will have to convert it to another format such as hex format or ASCII collating sequence.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 11:37:34 GMT

after running the code as per your suggested i am getting below output . can you please advise is the below data has unseen characters pos is '0' and uns '.' for all records on that particular filed please

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 11:47:18 GMT

uploaded please have a look and have marked red due to confidentiality purpose please .

for uns=. it's whole log has only missing values for uns which is .

Re: Identify unseen characters and resolve in SAS dataset for a particular column

FreelanceReinh — Tue, 23 Mar 2021 12:13:52 GMT

Please show the results of my code as well, e.g., the output of the suggested PROC PRINT step using dataset WANT. Your latest screenshots belong to kawakami's code. Ideally, copy and paste the output into a code box opened with the </> ("Insert Code") button. This is better than uploading screenshots.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

Tom — Tue, 23 Mar 2021 12:46:11 GMT

Use the $HEX format to look at what characters are in the file.

Personally I would check for any character that does not fall into the normal range of ASCII codes from '20'x (space) to '7F'x (tilde). One way to do that is to compress out the normal characters and then if the resulting string has anything in it they are the other characters.

For example if you have a dataset named HAVE with a character variable named STRING then code will create a dataset with just the observations where STRING had some strange characters.

data want ;
  set have ;
  bad_chars = compress(string,collate(020x,07Fx));
  length = lengthn(bad_chars);
  if length ;
  keep length bad_chars string ;
run;

Example:

data have ;
  length string $20 ;
  string = 'abc';
  output;
  string = 'abc' || 'A0'x ;
  output;
run;


data want ;
  set have ;
  bad_chars = compress(string,collate(020x,07Fx));
  length = lengthn(bad_chars);
  if length ;
  keep length bad_chars string ;
run;

proc print data=want;
  format bad_chars $hex. ;
run;

Obs    string                   bad_chars                    length

 1      abc      A020202020202020202020202020202020202020       1

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 13:43:50 GMT

Thanks you so much @Tom . i am able to see nearly 2000 records are having unseen characters please have a look on below screen shot. Actually from source itself iam getting the file like this.

Would you please advise how can i resolve this issue even if source file is sent with unseen characters please.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

Tom — Tue, 23 Mar 2021 13:48:42 GMT

Those all looks like zeros.

Try replacing the zeros with spaces

string = translate(string,' ','00'x);

and then re-check to see if there are other characters.

Re: Identify unseen characters and resolve in SAS dataset for a particular column

JJP1 — Tue, 23 Mar 2021 14:10:48 GMT

Thanks @Tom . rechecked i can see the below character values in output data. can i use translate function to resolve the both cases please

Re: Identify unseen characters and resolve in SAS dataset for a particular column

Tom — Tue, 23 Mar 2021 14:18:43 GMT

Sure. The TRANSLATE() function matches the two lists of characters by position. So to map both the '00'x and the 'FF'x characters to spaces use:

string = translate(string,'  ','00FF'x);