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);
I think the error is correct, you have unbalanced parentheses.
Count number of open/close parentheses. This is how I find these errors.
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.