Code for extracting data from meta table

mray — Fri, 21 Aug 2015 19:31:39 GMT

Hello,

I am a beginner with connecting to metatables and accessing the data from there. Usually I used to have the tables locally on my desktop.

I am confused about what is wrong in this code. Showing the code and the error generated as well as the desired output.

Desired output (just a snippet)-

SAS Code-

proc sql;

connect to oracle(authdomain="OracleAuth" buffsize=32767 preserve_comments path = 'roscop');

execute (alter session set nls_date_format = 'mm/dd/yyyy') by oracle ;

create table mray2 as

select *

from connection to oracle

(SELECT
CNTCT.CNTCT_OPEN_DT,
CNTCT.CNTCT_TYPE_DESC,
CNTCT.CNTCT_INIATR_DESC,
sum(decode(CNTCT.CNTCT_NEW_IND,'Y',1,0)),
CNTCT.SRC_SYS_DESC
FROM
CNTCT
WHERE
CNTCT.CNTCT_OPEN_DT BETWEEN '01-01-2015 00:00:00'dt AND '21-08-2015 00:00:00'dt
GROUP BY
CNTCT.CNTCT_OPEN_DT,
CNTCT.CNTCT_TYPE_DESC,
CNTCT.CNTCT_INIATR_DESC,
CNTCT.SRC_SYS_DESC

)

;

disconnect from oracle;

QUIT;

Error MSG-

ERROR: ORACLE prepare error: ORA-00905: missing keyword. SQL statement: SELECT CNTCT.CNTCT_OPEN_DT, CNTCT.CNTCT_TYPE_DESC,

CNTCT.CNTCT_INIATR_DESC, sum(decode(CNTCT.CNTCT_NEW_IND,'Y',1,0)), CNTCT.SRC_SYS_DESC FROM CNTCT WHERE CNTCT.CNTCT_OPEN_DT

BETWEEN '01-01-2015 00:00:00'dt AND '21-08-2015 00:00:00'dt GROUP BY CNTCT.CNTCT_OPEN_DT, CNTCT.CNTCT_TYPE_DESC,

Any help appreciated!

Thank you.

Monika

Re: Code for extracting data from meta table

SASKiwi — Sat, 22 Aug 2015 23:49:25 GMT

You are using SQL passthru so your SQL must conform to Oracle's requirements.

'01-01-2015 00:00:00'dt is not a valid Oracle datetime string. Try removing the DT on the end - this is SAS-specific syntax. Just using a date like '01-JAN-2015' may also work.

topic Re: Code for extracting data from meta table in SAS Enterprise Guide

Code for extracting data from meta table

Re: Code for extracting data from meta table