Connect to Oracle(user= , password=) code is not working in sql inline view

subrat1 — Wed, 20 Jul 2016 04:17:32 GMT

I have written a mcro using connect to oracle statement as shown below, but its showing error:

ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis.

Is there anytthing wrong in my code, it seems inline view code is not supported in connect to oracle statement.

/**/

%MACRO

WHICH(PROCESS,COND,SRC_SYS);

PROC SQL outobs=

10000;

connect to oracle (user=u1411710 password=work1sbu path=

'misp');

CREATE TABLE CURRENT.AGG_RISK_COV_&PROCESS AS

select *

from connection to oracle

(SELECT

S.*,T.RISK_STATE_CD,T.COVRG_TYPE_CD FROM

(SELECT

INTCOV_SK,

POLICY_SK,

ITEM_ATTRIBUTE_SK,

YEAR_CT,

MONTH_CT,

BROKER_NM,

SUM(EARNED_XPSR_AM) AS EARNED_XPSR_AM,

SUM(WRITTEN_XPSR_AM) AS WRITTEN_XPSR_AM,

SUM(MTD_WRITTEN_PRM_AM)AS MTD_WRITTEN_PRM_AM,

SUM(EARNED_PRM_AM) AS EARNED_PRM_AM

FROM

(

SELECT

POLICY_TYPE_SK,

SOURCE_SYSTEM_SK, ACTUAL_DT,

/* YEAR(DATEPART(ACTUAL_DT)) AS YEAR_CT,*/

EXTRACT(YEAR FROM ACTUAL_DT) as YEAR_CT,

/* MONTH(DATEPART(ACTUAL_DT)) AS MONTH_CT,*/

EXTRACT(MONTH FROM ACTUAL_DT) as MONTH_CT,

INTCOV_SK,

POLICY_SK,

ITEM_ATTRIBUTE_SK,

BROKER_NM,

EARNED_XPSR_AM,

WRITTEN_XPSR_AM,

MTD_WRITTEN_PRM_AM,

EARNED_PRM_AM

FROM misp.TCOVERAGES_SUMMARY

LEFT JOIN misp.TDATE

ON TCOVERAGES_SUMMARY.MONTH_SK = TDATE.DATE_SK

LEFT JOIN misp.TBROKER_DIMENSION

ON TBROKER_DIMENSION.BROKER_SK = TBROKER_DIMENSION.BROKER_SK

WHERE POLICY_TYPE_SK IN (

1,15) AND

SOURCE_SYSTEM_SK &COND IN (&

SRC_SYS.) AND

SNAPSHOT_TYPE_IN =

0 AND

EXTRACT(YEAR FROM ACTUAL_DT) >= &

START_YEAR. AND

EXTRACT(YEAR FROM ACTUAL_DT) <= &

END_YEAR.) AS R

GROUP BY

INTCOV_SK,

POLICY_SK,

ITEM_ATTRIBUTE_SK,

BROKER_NM,

YEAR_CT,

MONTH_CT ) AS S

LEFT JOIN

misp.TPOLICY

ON S.POLICY_SK = TPOLICY.POLICY_SK

LEFT JOIN

misp.TCOVRG_TYPE_DMNSN

ON S.INTCOV_SK = TCOVRG_TYPE_DMNSN.COVERAGE_TYPE_SK )AS T;

disconnect from oracle;

QUIT;

%MEND

;

OPTIONS

SYMBOLGEN MLOGIC MPRINT;

%LET

SRC_SYS_ALL = %STR(2,3,4,14,15);

%WHICH(PROCESS= PCG_&YYQ,COND=NOT,SRC_SYS=&SRC_SYS_ALL);

Re: Connect to Oracle(user= , password=) code is not working in sql inline view

Reeza — Wed, 20 Jul 2016 04:33:39 GMT

I think the error is correct, you have unbalanced parentheses.

Count number of open/close parentheses. This is how I find these errors.

Re: Connect to Oracle(user= , password=) code is not working in sql inline view

Ksharp — Wed, 20 Jul 2016 04:59:12 GMT

There must be some error in your SQL.

use the following option to check what kind of SQL SAS has passed into ORACLE .

options sastrace=',,,d' sastraceloc=log;

topic Re: Connect to Oracle(user= , password=) code is not working in sql inline view in SAS Programming

Connect to Oracle(user= , password=) code is not working in sql inline view

Re: Connect to Oracle(user= , password=) code is not working in sql inline view

Re: Connect to Oracle(user= , password=) code is not working in sql inline view