topic Re: one query not working in SAS but working in oracle in SAS Enterprise Guide

one query not working in SAS but working in oracle

deleted_user — Thu, 16 Apr 2009 13:29:01 GMT

hi,

select * from dim_account_type where (account_type,account_sub_type)
in (select account_type,account_sub_type from dim_account_type);

Thanks in adv

Re: one query not working in SAS but working in oracle

Doc_Duke — Thu, 16 Apr 2009 13:55:33 GMT

Oracle extended it's SQL beyond the ANSI Standard (every company does, just in different directions), so it's a difference that we are stuck with.

SAS has a section in the SQL reference about the differences between SAS SQL and ANSI SQL. I expect there is a similar document for Oracle SQL. Both support the complete ANSI standard (I think), but the extensions are useful, so you have to choose between portability and power.

Re: one query not working in SAS but working in oracle

RichardH_sas — Thu, 16 Apr 2009 15:56:03 GMT

Try this......
proc sql;
select *
from dim_account
where account_type in
(select account_type
from dim_account_type)
AND
account_sub_type in
(select account_sub_type
from dim_account_type);
quit;

As Doc mentioned, the Oracle code features an enhancement to the ANSI standard... this particular one isn't supported by SAS so you'd have to code around it as above. PROC SQL only allows a subquery to return a maximum of one column. Another alternative would be a join with an in-line view:

proc sql;
select d1.*
from dim_account as d1,
(select distinct account_type, account_sub_type
from dim_account_type) as d2
where d1.account_type=d2.account_type
and d1.account_sub_type=d2.account_sub_type;
quit;