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?
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