BookmarkSubscribeRSS Feed
subrat1
Fluorite | Level 6

 

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);

 

2 REPLIES 2
Reeza
Super User

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

 

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

Ksharp
Super User

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 834 views
  • 0 likes
  • 3 in conversation