SAS 9.4 playing up with IFN function results

Question

I recently moved to SAS 9.4, and found a weird bug with IFN and IFC fucntion. Please see the code below illustarting IFN fucntion:

data a;
input b 8.;
datalines;
0.1
150
110
9.1
1
0

;
run;

proc sql;
create table test as 
select b, (IFN(b = .  | b  > 100 | b  < 1 ,1,0)) as f_no_overlap length = 8,
(IFN(b in (.,0) | b > 100 | b  < 1 ,1,0)) as m_overlap length = 8,
(IFN(b in (.,0) | b  < 1 ,1,0)) as u_no_100,
(IFN(b in (0,110) | b  < 1 | b > 100 ,1,0)) as b_no_null,
(IFN(b = 0 | b  < 1 | b > 100 | b= . ,1,0)) as h_last_miss, 
(IFN(b = . | b = 0 | b  < 1 | b > 100,1,0)) as k_first_null,
(IFN(b = 0 | b = . | b  < 1 | b > 100,1,0)) as y_second_null,
(IFN(b = 0 | b  < 1 | b = . | b > 100,1,0)) as l_third_null,
(IFN(b  < 1 | b = 0 | b = . | b > 100,1,0)) as o_first_one from a;
quit;

I get the following results :

Where as the same code on SAS 9.3 gives the following results (which is correct) :

Why does it behave so weird with the same conditions on 9.4, mainly it seems to be the NULL in condition that seems to be causing an issue. Has anyone encountered the same problem? Do we have a solution for it?

If by "NULL" you mean "missing" then try checking what your missing value is set to (it might not be '.'). Try running proc options option = missing; run; and check the value displayed in the log. — Amir
Since there is nothing particular in that code that requires SQL try rewriting it as a data step and running in both versions of SAS. Also why are you using IFN() to generate boolean answers? You can replace ifn(condition,1,0) with just condition and SAS will still generate 1 for true and 0 for false.. — Tom
What version of SAS are you running? I cannot reproduce errors with sysvlong= 9.04.01M5P091317. — Tom
@Tom - I'm on SAS (r) Proprietary Software Release 9.4 TS1M4 . Can you tell me what version are you on since you mentioned you don't see these erroneous results? — Rhea

Richard Richard · Accepted Answer · 2019-03-12T10:30:11

The discussion of unexpected evaluation results is for code run in SAS 9.4 TS1M4 on Windows 10.0.17763 Build 17763.

3    proc options;
4    run;

    SAS (r) Proprietary Software Release 9.4  TS1M4

SQL does not have a missing value (., .<letter>) concept in the same way DATA Step and other Procs do. SQL has NULL and SAS coerces missing values into NULL, so there is a fuzzy edge and you found a problem there !

The failure to properly evaluate an expression appears to be how 9.4 SQL implementation is processing literal missing (.) values in this specific case. The failure is not in the IFN, but rather the evaluation passed to IFN !

Examining only the logic expression the problems does not seem to be related to IN. Similar unexpected evaluation results occur when the IN is split into a series of ORs. The specific causation appears to be where the missing literal (.) appears in the expression -- which in turn becomes 9.4 SQL implementation innards (parsing, etc.)

Definitely seems to be a bug when more than two sub-expressions and one of them uses a missing (.). The proper remedy, and one that becomes more suitable for remote or pass-through processing, would be to avoid using missing literals (.) in your SQL and use ANSI null tests operators IS NULL and IS NOT NULL

data have;
  b = 9.1;
run;

proc sql;
  create table want as
  select 
  b
  , b in (.,0) | b > 100                      as part1                        /* correct result */
  , b in (.,0)           | b < 1              as part2                        /* correct result */
  ,              b > 100 | b < 1              as part3                        /* correct result */
  , b in (.,0) | b > 100 | b < 1              as parts_null_first             /* INCORRECT result */
  ,              b > 100 | b < 1 | b in (.,0) as parts_null_last              /* INCORRECT result */
  , b=. | b=0  | b > 100 | b < 1              as parts_no_in_null_first       /* INCORRECT result */
  ,       b=0  | b > 100 | b < 1 | b= .       as parts_no_in_null_last        /* correct - weird? */

  , b is null | b=0 | b > 100 | b < 1 as parts_is_null                        /* correct result */

  , calculated part1 | calculated part2 | calculated part3 as calc_parts_in_1_expr /* correct result */

  from have
  ;
quit;

I didn't test if the same issue occurs when the problematic expression is in a WHERE caluse. The expression is not a problem as an assignment in DATA step:

data want2;
  set have;
  parts_null_first = b in (.,0) | b > 100 | b < 1 ;                 /* correct result  */
  parts_null_last  =              b > 100 | b < 1 | b in (.,0);     /* correct result */
run;

If the expression evaluation 'error' occurs in where expressions, then the where evaluation engine is more likely the root cause -- I believe the same engine is used for Proc/Data WHERE statements, Dataset WHERE= option and SQL evaluations.

There might be a SAS Note or Hotfix for the situation but I didn't go looking.

Another discussion of testing for missing values can be found in SAS_Tipster's "SAS Tip: Use IS MISSING and IS NULL with Numeric or Character Variables" on communities.sas.com. The important take away is the use of operators in criteria testing for null values.

The IS MISSING and IS NULL operators, which are used with a WHERE statement, can handle character or numeric variables. They also work with the NOT operator:

Documentation summarizes IS MISSING predicate as:

Tests for a SAS missing value in a SAS native data store.

SAS 9.4 playing up with IFN function results

2 Answers