If an SQL procedure view is referenced in a FROM clause, incorrect results might occur when the sum of a CALCULATED column is used in a CASE expression. If a table is used in the FROM clause instead of a view, the correct results are returned.

Click the Full Code tab to see code that illustrates the difference in results when a table is queried instead of a view.

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

Operating System and Release Information

data alfa;
a =1; b =1; c =1;
testvar=100;
run;

proc sql;
create view alfa2 as
select 
             (case when a not in (0,.) then 1 else 0 end) as ctr121_1 length = 8,
             (case when a not in (0,.) then 1 else 0 end) as ctr121_2 length = 8,
             (case when a not in (0,.) then 1 else 0 end) as ctr121_3 length = 8,
			 testvar

from alfa;
quit;

proc sql;
create table wrong as
select ctr121_1, ctr121_2, ctr121_3,
case when ctr121_1 =0 then 0 else 1 end as accumulo_ctr121_1 length = 8, 
case when ctr121_2 =0 then 0 else sum(calculated accumulo_ctr121_1,1) end as accumulo_ctr121_2 length = 8,
case when ctr121_3 =0 then 0 else sum(calculated accumulo_ctr121_2,1) end as accumulo_ctr121_3 length = 8
from alfa2;
quit;

proc sql;
create table correct as
select ctr121_1, ctr121_2, ctr121_3,
case when ctr121_1 =0 then 0 else 1 end as accumulo_ctr121_1 length = 8, 
case when ctr121_2 =0 then 0 else sum(testvar,1) end as accumulo_ctr121_2 length = 8,
case when ctr121_3 =0 then 0 else sum(testvar,1) end as accumulo_ctr121_3 length = 8
from alfa;
quit;
 
proc print data=wrong;
run;

proc print data=correct;
run;

A fix for this issue for Base SAS 9.4_M3 is available at: