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.
where account_type in
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:
from dim_account as d1,
(select distinct account_type, account_sub_type
from dim_account_type) as d2