Missing Variables Not Recognised in code

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

Missing Variables Not Recognised in code

[ Edited ]

I have an issue where I'm trying to identify observations where a particular character variable is missing using the below method, but my data step returns 0 obs. However, when I view the original dataset in the session and apply the same statement in the Command Bar, it finds the missing variables. Any ideas? I've tried varying the quotes with/without a space and using double quotes to no avail.

 

data a;

   set b;

      where var = ' ';

run;

(Returns 0 obs)

 

And in the Command Bar:

where var = ' '

(Returns several obs)


Accepted Solutions
Solution
‎07-19-2016 08:21 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Missing Variables Not Recognised in code

Without seeing your data its hard to say (thats the data in a datastep so it matches exactly your data), could be formats, could be spaces etc.

You can try a couple of things:

First of, use missing() function:

data a;
   set b;
   if missing(var);
run;

Or compress()

data a;
   set b;
   if compress(var)='';
run;

Or strip() - same as compress() above.

View solution in original post


All Replies
Solution
‎07-19-2016 08:21 AM
Esteemed Advisor
Esteemed Advisor
Posts: 7,253

Re: Missing Variables Not Recognised in code

Without seeing your data its hard to say (thats the data in a datastep so it matches exactly your data), could be formats, could be spaces etc.

You can try a couple of things:

First of, use missing() function:

data a;
   set b;
   if missing(var);
run;

Or compress()

data a;
   set b;
   if compress(var)='';
run;

Or strip() - same as compress() above.

New Contributor
Posts: 2

Re: Missing Variables Not Recognised in code

Thanks RW9 - compress(var) did the trick!

Super User
Super User
Posts: 6,383

Re: Missing Variables Not Recognised in code

COMPRESS() should not make any difference.  It will only remove the blanks. But SAS comparison operator already ignores trailing blanks.

 

Most likely the change is because you are referencing data from an external database. SAS will try to push the WHERE clause into the external database. So in the external database there could be a difference between a null string, a string with one blank, a string with two blanks, etc.  Changing from WHERE to IF will insure that the comparison is done on the SAS side.

 

You might see the same effect by adding the COMPRESS() function to a WHERE statement. If SAS does not know how to push the function into the source database then it will apply the WHERE clause on the SAS side and the comparison will work.

 

 

☑ This topic is SOLVED.

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

Discussion stats
  • 3 replies
  • 249 views
  • 2 likes
  • 3 in conversation