Is it possible to use CASE expressions in a sas oracle pass-through?

Question

I'm using a SAS-Oracle Pass-Through query with a left join. Is it possible to use a CASE statement in the select? Here an example:

proc sql;
connect to oracle (user=&user. password=&password. buffsize=1000 path=XXX);
create table
lib.test
as select
*
from connection to oracle (
select
 a.variable1
,a.variable2
,a.variable3
,b.variable4
,case when b.variable5 = 'K' and b.variable6 < b.variable7 then b.variable6 else . end as variable_new
from table1 a
left join table2 b
on
a.id=b.id
where 
a.variable5 = 'X'
group by
a.variable1, a.variable2, a.variable2, b.variable4, b.variable_new
);
disconnect from oracle;
quit;

Without the codeline with the case statement the code runs without a problem. So I think there has to be changed something. Is it possible to use a case expression here? Or is there another way to do this? Any help is greatly appreciated!

Thank you for your help so far! As I mentioned in the comments, I could now run my code by adding the case statement also to the group by. My new question now is: I have several case statements and therefore create several new columns to my dataset. As I have to include all variables (that are not a sum, count etc.) in the group by statement, I added all the case statements to the group by. But now the lines are doubled, which I don't want. E.g.

select
 a.variable1
,a.variable2
,a.variable3
,b.variable4
,case when b.variable5 = 'K' and b.variable6 < b.variable7 then b.variable6 end as variable_new
,case when b.variable5 = 'K' and b.variable8 >= b.variable9 then b.variable6 end as variable_new_2
from table1 a
left join table2 b
on
a.id=b.id
where 
a.variable5 = 'X'
group by
a.variable1, a.variable2, a.variable2, b.variable4
,case when b.variable5 = 'K' and b.variable6 < b.variable7 then b.variable6 end
,case when b.variable5 = 'K' and b.variable8 >= b.variable9 then b.variable8 end 
);
disconnect from oracle;

the result then is of the form

 variable1  variable2  variable3 variable4  variable_new  variable_new_2
1                                            variable6    .
1                                               .           variable8 

instead of just one line like this

 variable1  variable2  variable3 variable4  variable_new  variable_new_2
1                                            variable6      variable8 

How can I group the data like that?

Answer 1

Using period to indicate a missing value is SAS syntax. In Oracle you would probably want to use the keyword NULL instead.

,case when b.variable5 = 'K' and b.variable6 < b.variable7 then b.variable6
      else null
 end as variable_new