How to use null as condition in 'if then else' statement in SAS?

user_9500618 0
Viewed 6489

The logic is : If variable X is null, then output to dataset DATA. But 'is null' can't be used in 'if' statement in SAS. How can I realize the logic in SAS?

sas
2 Answers

The syntax is different in a data step than in sql; (This is because SAS is older than SQL)

The most correct answer

data FILTERED;
   set UNFILTERED;
   if missing(X) then output;
run;

Alternatives

The shorthand

You can also use if missing(X);, a short hand for if not missing(X) then delete;.

The where clause

See the answer given by user "itsMeInMiami" and my response

Using literals

In SAS Character variables can't assume a special value to indicate they are missing if missing(MyCharVar) is actually a shorthand for if MyCharVar = ''.

Numeric variables, however can assume assume different "null" values, read more here.

In practice, only one is used, and it has a literal .:

  • it is the initial value of a numeric variable before it is assigned
  • it is the result of a division by zero
  • it is the result of calculations on missing variables
  • it is the result of an outer join if there is no observation on the table the value should come from

So usually if missing(MyNumVar) is actually a shorthand for if MyNumVar= ., but you might run into a situation where a nerd has been fiddling with the other special values to indicated why a value is actually missing and if MyNumVar= . is wrong.

user_43856470

Dirk Horsten's answer is right on target. Another option, which may be faster, is:

data FILTERED;
   set UNFILTERED;
   where missing(X);
run;

The data step where syntax is close to a SQL where clause. That is, it works on variables that already exist in the dataset (not those calculated as part of the data step processing). If the SAS dataset is indexed, on the variables referenced in the where statement, you can frequently get faster performance.

user_87290300
Related