Help using Base SAS procedures

Tricky Blanks

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

Tricky Blanks

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?


Accepted Solutions
Solution
‎12-05-2012 01:31 PM
Super User
Posts: 5,085

Re: Tricky Blanks

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.

View solution in original post


All Replies
Super User
Posts: 17,871

Re: Tricky Blanks

Try

if not missing(comment);

OR

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.

Super Contributor
Posts: 1,636

Re: Tricky Blanks

try:

if comment=' ' then delete;

Solution
‎12-05-2012 01:31 PM
Super User
Posts: 5,085

Re: Tricky Blanks

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.

Occasional Contributor
Posts: 6

Re: Tricky Blanks

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!

Super User
Posts: 5,085

Re: Tricky Blanks

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.

Super User
Posts: 9,687

Re: Tricky Blanks

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

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

Ksharp

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 280 views
  • 7 likes
  • 5 in conversation