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

[JJP1]

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

[FreelanceReinh]

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.

[JJP1]

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 

[FreelanceReinh]

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.

[japelin]

try use anycntrl.

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

[JJP1]

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 

[japelin]

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.

[JJP1]

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 ?

or

 am i wrong please ?

[FreelanceReinh]

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?

[JJP1]

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

[FreelanceReinh]

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.

[japelin]

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.

[JJP1]

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 . 

[Tom]

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

[JJP1]

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.