topic Error Proc Sql connect to in SAS Programming

Error Proc Sql connect to

jhh197 — Wed, 18 Aug 2021 02:58:32 GMT

PROC SQL;

CONNECT to ORACLE as EX(authdomain=yyyy path=xxx connection=global);

CREATE TABLE TEST20

SELECT * FROM CONNECTION TO EX (

WITH TMP_HD AS

(

SELECT

CDR.* ,

MF.DATE,

MF.CD

FROM

SP_V.TEST1 CDR

LEFT JOIN SP_V.TEST2 MF

ON MF.ID=CDR.ID

WHERE

CDR.ID =100

TMP_SPL AS

(

SELECT

SPL.REGION,

PCS.TYPE

FROM SP_V.TEST3 SPL

JOIN TMP_HD S

ON SPL.REGION = S.REGION

JOIN SP_V.TEST4 PCS

ON SPL.REGION = PCS.REGION

)

SELECT H.*, F.*

FROM TMP_HD H

LEFT JOIN TMP_SPL F

ON H.REGION = F.REGION

);

DISCONNECT FROM EX ;

QUIT;

When I run above code it gives below error

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

Can anyone please help .

Re: Error Proc Sql connect to

ChrisNZ — Wed, 18 Aug 2021 03:44:26 GMT

Please reformat your code.

As it is, maybe a rogue character has entered it, probably in one of the WITH clauses, and maybe near the end. Remove all spaces and retype them.

A proper editor such as Notepad++ can display all the characters.

Re: Error Proc Sql connect to

SASKiwi — Wed, 18 Aug 2021 22:00:31 GMT

The error is perfectly obvious. Count the number of left parentheses (4) and the number of right parentheses (3) in the posted code. You are missing one right parentheses to balance your syntax. As to where it should go I'll leave that up to you as you are using Oracle-specific syntax and type of joining you are doing is not clear to me.

Re: Error Proc Sql connect to

ChrisNZ — Thu, 19 Aug 2021 05:50:57 GMT

I must be blind, I see balanced parentheses.