SAS sees the same value as 2 different values

[axescot78]

I have 2 data sets that I am trying to link by an alphanumeric variable (job code). I already found that one data set contains "non-breaking spaces" (which I didn't know exist). I am finding some of the codes still are not matching. For example, I have a code "AB000". When I do a proc freq before linking these data sets, I find two "AB000" listed. The search function in SAS results window finds both entries using "AB000". I also tried matching with "%AB000%" and using compress to account for any characters that I may not be seeing, and still getting the same result. I tried exporting the proc freq to excel and original data set to csv, and still I am not seeing a difference. Has anyone had experience with this?

[ballardw · Accepted solution]

The proc freq output is likely showing two, or more values, because of leading spaces. The table generator for output left justifies the text even if there are leading spaces.

Try using the LEFT or STRIP function on the variable(s) in a data step and then see if the result changes.

A brief example to demonstrate:

data example;  
   length string $ 8;
   string='abc';output;
   string=' abc';output;
   string='   abc';output;
run;

proc freq data=example;
   tables string;
run;

data newex;
   set example;
   string=strip(string);
run;
proc freq data=newex;
   tables string;
run;

[Tom]

Are you looking at the output produced by ODS (such as HTML, PDF or RTF)?  If then ODS has a nasty habit of not displaying leading spaces.

Try just removing the leading spaces.

data fix;
  set have;
  job_code = left(job_code);
run;

Otherwise try looking at the values using the $HEX format to see the actual characters in the field.

proc freq data=fix ;
  where job_code like '%ABOOO%';
  tables job_code / noprint out=counts;
run;
proc print data=counts;
  format job_code $hex14. ;
run;

[Tom]

Also make sure that those oval shaped characters are all the same.

Old typewriters did not have keys for one or zero as you could just use the lowercase L and uppercase o to put the same ink on the page.