topic Re: Tricky Blanks in SAS Procedures

Tricky Blanks

keeks137 — Wed, 05 Dec 2012 18:17:43 GMT

Hey everyone! Completely new here. Thank you in advance for the help.

I can not for the life of me figure out why I can't eliminate records that are blank in a certain variable from my data pull. I'm trying to eliminate all records that leave the "comment" field (a text variable) blank by using this code:

if comment ne '';

I was still seeing blanks show up in my data, so I figured there were some records where people just entered a space or tab, and therefore SAS didn't consider them blank. I tried the following two solutions (separately) to eliminate those records:

clean_comment=strip(comment);

if clean_comment ne '';

clean_comment=compbl(comment);

if clean_comment ne '';

I'm still seeing the blanks show up. Is there a way I can figure out why those pesky blanks aren't dropping out?

Re: Tricky Blanks

Reeza — Wed, 05 Dec 2012 18:26:30 GMT

Try

if not missing(comment);

if not missing(compress(comment));

If that doesn't work, you can attach a sample of your excel file just a few of the comment fields to test.

Re: Tricky Blanks

Linlin — Wed, 05 Dec 2012 18:26:31 GMT

try:

if comment=' ' then delete;

Re: Tricky Blanks

Astounding — Wed, 05 Dec 2012 18:31:49 GMT

Most likely, you have diagnosed the problem correctly. There is something nonblank in there, like a tab or a carriage return. It displays as a blank, but it isn't actually a blank so any statement that removes blanks will not remove it.

The first step is to diagnose what is really in there. Take a few troublesome lines and try:

troublesome_characters = put(compress(comment), $hex6.);

Then print the troublesome_characters.

That should give enough information to diagnose what the characters are. Once that it is known, removing them will be relatively simple.

Good luck.

Re: Tricky Blanks

keeks137 — Wed, 05 Dec 2012 18:58:06 GMT

This did the trick!

I'm not going to pretend like I understand what a carriage return is but that seemed to be the case. Your troublesome_characters code above returned a blank value for the blanks that were being erased, and "0D" for those that weren't. I looked up "0D" and that appears to be a carriage return.

So, I wrote the following code to fix the issue. Please feel free to suggest a more efficient way of doing it.

if comment ne ''; <== Eliminates the normal blanks

trouble = put(compress(comment), $hex6.); <== Identifies carriage returns (blanks where trouble="0D")

if trouble ne '0D'; <== Eliminates carriage returns

Everyone was so helpful. Thank you very much!

Re: Tricky Blanks

Astounding — Wed, 05 Dec 2012 19:05:10 GMT

Good work! I promise, posters here appreciate when you take the initiative and run with it.

I would suggest a slightly different route:

comment = compress(comment, '0D'x);

if comment ne ' ';

That way, you also remove any carriage returns that appear at the end of nonblank comments.

Depending on how the data gets read in initially, there may be ways to eliminate the carriage returns earlier in the process. But that's another story.

Re: Tricky Blanks

Ksharp — Thu, 06 Dec 2012 02:42:56 GMT

Or using peal regular expression can take care of more "blank character".

comment=prxchange('s/\s+//o',comment,-1);

Ksharp