Syntex Error but no clue where

[Ahsan]

Hi I am trying to run this code and keep getting Syntex error and I don't know where the syntax error is am I missing something.

%LET SDOS = To_Date('01AUG2018','ddmonyyyy');

%LET EDOS = To_Date('31AUG2018','ddmonyyyy');

/*%LET SFY = AUG2018;*/

proc sql ;

connect to oracle(authdomain=oraAuth path="coho" readbuff=10000);

CREATE TABLE test.SUPPLEMENT AS select * from connection to oracle

(SELECT HEAD.TRNSCT_CNTL_NBR

,LINE.CLM_LNE_NBR

,HEAD.CLM_HDR_TRNSCT_TYP_CD

,HEAD.HDR_TYP_CD

,HEAD.BTCH_JULI_DT_NBR

,HEAD.CLM_BTCH_DOC_TYP_CD

,HEAD.CLM_HDR_PD_DT

,HEAD.CLM_PTNT_STS_CD

,LINE.LNE_SBMT_CHRG_AMT

,LINE.LNE_RMBRS_UNT_AMT

,HEAD.PRINCIPLE_DIAG_CD

,HEAD.ALT_MBR_ID_DECRYPT

,HEAD.MBR_ID

,MEMELIG.PRVDR_PCP_ID as PCP_ID

,LINE.DRUG_CD

,LINE.RNDR_PRVDR_NPI

,LINE.RNDR_PRVDR_TXNMY_CD

,LINE.RNDR_PRVDR_LOC_CD

,HEAD.BILL_PRVDR_LOC_CD

,CASE WHEN HEAD.HDR_TYP_CD='R' THEN HEAD.PRI_APRV_NBR

WHEN HEAD.HDR_TYP_CD <>'R' THEN LINE_PA.PRI_APRV_NBR

ELSE is null END AS PRI_APRV_NB

,CHCOND.COND_1_CD AS CONDITION_CODE_1

,CHCOND.COND_2_CD AS CONDITION_CODE_2

,CHCOND.COND_3_CD AS CONDITION_CODE_3

,CHCOND.COND_4_CD AS CONDITION_CODE_4

FROM CLAIM_HEADER_CUR AS HEAD

INNER JOIN CLAIM_LINE_CUR AS LINE

ON (HEAD.TRNSCT_CNTL_NBR = LINE.TRNSCT_CNTL_NBR

AND HEAD.CLM_HDR_PD_DT = LINE.CLM_HDR_PD_DT

AND LINE.HLTHPLN_ID IN (1,2)

AND (HEAD.HDR_TYP_CD BETWEEN 'A' AND 'L' OR

HEAD.HDR_TYP_CD BETWEEN 'N' AND 'Z' OR

HEAD.HDR_TYP_CD BETWEEN '0' AND '3' OR

HEAD.HDR_TYP_CD BETWEEN '5' AND '9')

AND HEAD.CLM_BTCH_DOC_TYP_CD = 'C'

AND HEAD.CLM_HDR_PD_DT BETWEEN &SDOS. AND &EDOS.))

LEFT JOIN CLAIM_LINE_PA_CUR AS LINE_PA

ON HEAD.TRNSCT_CNTL_NBR = LINE_PA.TRNSCT_CNTL_NBR

AND HEAD.CLM_HDR_PD_DT = LINE_PA.CLM_HDR_PD_DT

AND LINE.CLM_LNE_NBR = LINE_PA.CLM_LNE_NBR

LEFT JOIN MEMBER_ELIGIBILITY_CUR AS MEMELIG

ON HEAD.MBR_ID = MEMELIG.MBR_ID

AND HEAD.SVC_BGN_DT BETWEEN MEMELIG.ELGB_BGN_DT AND MEMELIG.ELGB_END_DT

AND (HEAD.PCP_PRVDR_NPI = MEMELIG.PCP_NPI

OR HEAD.PCP_PRVDR_ATYP_PRVDR_NBR=MEMELIG.PCP_NPI)

AND LINE.HLTHPLN_ID = MEMELIG.HLTHPLN_ID

AND MEMELIG.MBR_RCRD_STS_CD = 'A'

AND MEMELIG.PRVDR_PCP_ID is not null

LEFT JOIN CLAIM_HEADER_COND_PIVOT_CUR AS CHCOND

ON CHCOND.TRNSCT_CNTL_NBR = HEAD.TRNSCT_CNTL_NBR

AND CHCOND.CLM_HDR_PD_DT = HEAD.CLM_HDR_PD_DT

);

disconnect from oracle;

QUIT;

NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR

20

21 LIBNAME Test "/sas/RA/truven/Ahsan" COMPRESS=YES;

NOTE: Libref TEST was successfully assigned as follows:

Engine: V9

Physical Name: /sas/RA/truven/Ahsan

22

23 %LET SDOS = To_Date('01AUG2018','ddmonyyyy');

24 %LET EDOS = To_Date('31AUG2018','ddmonyyyy');

25 /*%LET SFY = AUG2018;*/

26

　

27 proc sql ;

28 connect to oracle(authdomain=oraAuth path="coho" readbuff=10000);

29 CREATE TABLE test.SUPPLEMENT AS select * from connection to oracle

30 (SELECT HEAD.TRNSCT_CNTL_NBR

31 ,LINE.CLM_LNE_NBR

32 ,HEAD.CLM_HDR_TRNSCT_TYP_CD

33 ,HEAD.HDR_TYP_CD

34 ,HEAD.BTCH_JULI_DT_NBR

35 ,HEAD.CLM_BTCH_DOC_TYP_CD

36 ,HEAD.CLM_HDR_PD_DT

37 ,HEAD.CLM_PTNT_STS_CD

38 ,LINE.LNE_SBMT_CHRG_AMT

39 ,LINE.LNE_RMBRS_UNT_AMT

40 ,HEAD.PRINCIPLE_DIAG_CD

41 ,HEAD.ALT_MBR_ID_DECRYPT

42 ,HEAD.MBR_ID

43 ,MEMELIG.PRVDR_PCP_ID as PCP_ID

44 ,LINE.DRUG_CD

45 ,LINE.RNDR_PRVDR_NPI

46 ,LINE.RNDR_PRVDR_TXNMY_CD

47 ,LINE.RNDR_PRVDR_LOC_CD

48 ,HEAD.BILL_PRVDR_LOC_CD

49 ,CASE WHEN HEAD.HDR_TYP_CD='R' THEN HEAD.PRI_APRV_NBR

50 WHEN HEAD.HDR_TYP_CD <>'R' THEN LINE_PA.PRI_APRV_NBR

51 ELSE is null END AS PRI_APRV_NB

52 ,CHCOND.COND_1_CD AS CONDITION_CODE_1

2 The SAS System 08:46 Thursday, September 20, 2018

53 ,CHCOND.COND_2_CD AS CONDITION_CODE_2

54 ,CHCOND.COND_3_CD AS CONDITION_CODE_3

55 ,CHCOND.COND_4_CD AS CONDITION_CODE_4

56 FROM CLAIM_HEADER_CUR AS HEAD

57 INNER JOIN CLAIM_LINE_CUR AS LINE

58 ON (HEAD.TRNSCT_CNTL_NBR = LINE.TRNSCT_CNTL_NBR

59 AND HEAD.CLM_HDR_PD_DT = LINE.CLM_HDR_PD_DT

60 AND LINE.HLTHPLN_ID IN (1,2)

61 AND (HEAD.HDR_TYP_CD BETWEEN 'A' AND 'L' OR

62 HEAD.HDR_TYP_CD BETWEEN 'N' AND 'Z' OR

63 HEAD.HDR_TYP_CD BETWEEN '0' AND '3' OR

64 HEAD.HDR_TYP_CD BETWEEN '5' AND '9')

65 AND HEAD.CLM_BTCH_DOC_TYP_CD = 'C'

66 AND HEAD.CLM_HDR_PD_DT BETWEEN &SDOS. AND &EDOS.))

67 LEFT JOIN CLAIM_LINE_PA_CUR AS LINE_PA

68 ON HEAD.TRNSCT_CNTL_NBR = LINE_PA.TRNSCT_CNTL_NBR

69 AND HEAD.CLM_HDR_PD_DT = LINE_PA.CLM_HDR_PD_DT

70 AND LINE.CLM_LNE_NBR = LINE_PA.CLM_LNE_NBR

71 LEFT JOIN MEMBER_ELIGIBILITY_CUR AS MEMELIG

72 ON HEAD.MBR_ID = MEMELIG.MBR_ID

73 AND HEAD.SVC_BGN_DT BETWEEN MEMELIG.ELGB_BGN_DT AND MEMELIG.ELGB_END_DT

74 AND (HEAD.PCP_PRVDR_NPI = MEMELIG.PCP_NPI

75 OR HEAD.PCP_PRVDR_ATYP_PRVDR_NBR=MEMELIG.PCP_NPI)

76 AND LINE.HLTHPLN_ID = MEMELIG.HLTHPLN_ID

77 AND MEMELIG.MBR_RCRD_STS_CD = 'A'

78 AND MEMELIG.PRVDR_PCP_ID is not null

79 LEFT JOIN CLAIM_HEADER_COND_PIVOT_CUR AS CHCOND

80 ON CHCOND.TRNSCT_CNTL_NBR = HEAD.TRNSCT_CNTL_NBR

81 AND CHCOND.CLM_HDR_PD_DT = HEAD.CLM_HDR_PD_DT

82 );

_

22

200

ERROR 22-322: Syntax error, expecting one of the following: ;, !, !!, &, *, **, +, ',', -, /, <, <=, <>, =, >, >=, AND, ANSIMISS,

CROSS, EQ, EQT, EXCEPT, FULL, GE, GET, GROUP, GT, GTT, HAVING, INNER, INTERSECT, JOIN, LE, LEFT, LET, LT, LTT,

NATURAL, NE, NET, NOMISS, NOT, OR, ORDER, OUTER, RIGHT, UNION, WHERE, ^, ^=, |, ||, ~, ~=.

ERROR 200-322: The symbol is not recognized and will be ignored.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

83 disconnect from oracle;

NOTE: Statement not executed due to NOEXEC option.

84 QUIT;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.09 seconds

cpu time 0.01 seconds

85

86

87 %LET _CLIENTTASKLABEL=;

88 %LET _CLIENTPROJECTPATH=;

89 %LET _CLIENTPROJECTNAME=;

90 %LET _SASPROGRAMFILE=;

91

92 ;*';*";*/;quit;run;

93 ODS _ALL_ CLOSE;

94

3 The SAS System 08:46 Thursday, September 20, 2018

95

96 QUIT; RUN;

97

[ballardw · Accepted solution]

First thing, post code or log entries into a code box opened with the {I} to preserve any formatting of your code.

The error is telling you that the final ) is mismatched. Since it doesn't have a matching ( then something other than a ) is expected.

How do I know there isn't a matching ( , you may ask? The SAS Enhanced Editor will move the cursor to the matching ( or ) if the cursor is placed on at the character and then CTRL-  or CTRl 0 is pushed.

So you need to examine which ( you were expecting it to close with the final ).

If the intent was to close the selecting starting on the 4th line of the proc sql code (line 30 in the log)

(SELECT HEAD.TRNSCT_CNTL_NBR

that is closed at this line:

AND HEAD.CLM_HDR_PD_DT BETWEEN &SDOS. AND &EDOS.))

or line 66 in the log.

I can't tell what other logic you might have wanted with 3 left joins and an inner join it is not obvious what your actual desire might be.

[Kurt_Bremser]

Writing "code" like that (unstructured spaghetti of all-uppercase) is a sure-fire way to get such problems.

I share the office with a lot of PL/1 and SQL programmers, and I can guarantee you that encountering code like that will lead to outbursts of very profane language.