BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
keeks137
Calcite | Level 5

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?

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

6 REPLIES 6
Reeza
Super User

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.

Linlin
Lapis Lazuli | Level 10

try:

if comment=' ' then delete;

Astounding
PROC Star

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.

keeks137
Calcite | Level 5

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!

Astounding
PROC Star

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.

Ksharp
Super User

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

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

Ksharp

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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