Problem Note 59356: Using a CASE WHEN clause with no ELSE clause generates incorrect results in SAS® Scalable Performance Data Server
 |  |  |  |  |  |
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
| Product Family | Product | System | Product Release | SAS Release | ||
| Reported | Fixed* | Reported | Fixed* | |||
| SPDS | SAS Scalable Peformance Data Server | Microsoft® Windows® for x64 | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |
| Microsoft Windows Server 2012 R2 Datacenter | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| Microsoft Windows Server 2012 R2 Std | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| Windows 7 Enterprise x64 | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| 64-bit Enabled AIX | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| 64-bit Enabled Solaris | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| HP-UX IPF | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| Linux for x64 | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
| Solaris for x64 | 5.1 | 5.1 HF7 | 9.4 TS1M0 | |||
Modify the macro variables to match your environment setting before submitting this code.
%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:
https://tshf.sas.com/techsup/download/hotfix/HF2/U88.html#59356A fix for this issue for SAS Scalable Performance Data Server 5.1 is available at:
https://tshf.sas.com/techsup/download/hotfix/HF2/N49.html#59356| Type: | Problem Note |
| Priority: | alert |
| Date Modified: | 2016-12-14 16:35:08 |
| Date Created: | 2016-11-16 07:58:17 |