SAS: Where statement not working with string value

0
votes

I'm trying to use PROC FREQ on a subset of my data called dataname. I would like it to include all rows where varname doesn't equal "A.Never Used". I have the following code:

proc freq data=dataname(where=(varname NE 'A.Never Used')); run;

I thought there might be a problem with trailing or leading blanks so I also tried:

proc freq data=dataname(where=(strip(varname) NE 'A.Never Used')); run;

My guess is for some reason my string values are not "A.Never Used" but whenever I print the data this is the value I see.

1
saswhere

1 Answers

2
votes

This is a common issue in dealing with string data (and a good reason not to!). You should consider the source of your data - did it come from web forms? Then it probably contains nonbreaking spaces ('A0'x) instead of regular spaces ('20'x). Did it come from a unicode environment (say, Japanese characters are legal)? Then you may have transcoding issues.

A few options that work for a large majority of these problems:

  1. Compress out everything but alphabet characters. where=(compress(varname,,'ka') ne 'ANeverUsed') for example. 'ka' means 'keep only' and 'alphabet characters'.
  2. UPCASE or LOWCASE to ensure you're not running into case issues.
  3. Use put varname HEX.; in a data step to look at the underlying characters. Each two hex characters is one alphabet character. 20 is space (which strip would remove). Sort by varname before doing this so that you can easily see the rows that you think should have this value next to each other - what is the difference? Probably some special character, or multibyte characters, or who knows what, but it should be apparent here.