When you use a CASE WHEN clause with no ELSE clause in your query, incorrect results are generated in the SAS SPD Server.

Click the Full Code tab in this SAS Note to see an example of code that demonstrates the incorrect results as reported in the following SAS log:

31   data _null_;
32     set xyz;
33     put _all_;
34   run;

a=1 b=one _ERROR_=0 _N_=1
a=1 b=one _ERROR_=0 _N_=2
a=2 b=one _ERROR_=0 _N_=3

NOTE: There were 3 observations read from the data set WORK.XYZ.

Note that when variable a is set to 2, variable b is incorrectly set to one and must be set to missing value.

Click the Hot Fix tab in this note to access the hot fix for this issue.

Operating System and Release Information

%let dom=mydomain;
%let host=myhost.domain;
%let portno=5200;


libname foo sasspds "&dom" host="&host" serv="&portno" user='anonymous';

data foo.tbl;
  a=1;output;
  a=1;output;
  a=2;output;
run;

proc sql;

 connect to sasspds (dbq='&dom' host='&host' serv='&portno' user='anonymous');

     create table xyz as select * from connection to sasspds 

       (select a, (CASE WHEN a=1 THEN 'one' END) as b from  tbl order by a);
     
quit;
 

data _null_;
  set xyz;
  put _all_;
run;

A fix for this issue for SAS Scalable Performance Data Server 5.2 is available at:

A fix for this issue for SAS Scalable Performance Data Server 5.1 is available at: