Not able to use oracle function in SAS

arunrami — Wed, 24 Apr 2019 09:17:10 GMT

Dear SASian,

I want to incorporate below query in SAS but I am getting syntax error due to oracle functions (get_xml) am using , can any one please guide how to overcome this.

proc sql;
create table record_3033 as
select owner, table_name, 
to_number(extractvalue(xmltype(dbms_xmlgen.getxml('select /*+ parallel('||table_name||',10) */   count(*) c from '||owner||'.'||table_name||' where '||owner||'.'||table_name||'.glb_oe_id = 777')),'/ROWSET/ROW/C'))
AS COUNT
  from db.all_tables
  where owner = 'DWHDWH' and table_name in (select distinct dwh_tbl_nm from db.etl_cntrl_tbl ) order by table_name;
  quit;

Re: Not able to use oracle function in SAS

LinusH — Wed, 24 Apr 2019 11:02:45 GMT

The key to this is "oracle function in SAS".

No software fully support another vendors software. If you you are using SAS, use SAS functions.

To use Oracle functions, use Oracle. From SAS this can be done using SQL explicit pass through.

topic Re: Not able to use oracle function in SAS in SAS Programming

Not able to use oracle function in SAS

Re: Not able to use oracle function in SAS