Desktop productivity for business analysts and programmers

Where to put parenthesis per the error

Reply
Frequent Contributor
Posts: 135

Where to put parenthesis per the error

proc sql ;

584 CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");

585 CREATE TABLE &NAME..CLMEXZ01 AS

586 SELECT * FROM CONNECTION TO tera

587 (SELECT

588 INST_CLM_LN.CLM_LINE_ID AS CLMLN,

589 INST_CLM_LN.CLM_BGN_SRVC_DT AS LBEGDT,

590 INST_CLM_LN.CLM_END_SRVC_DT AS LENDDT,

591 INST_CLM_LN.MCS_AT_RISK AS RISKIND,

592 INST_CLM_LN.PROV_TOT_CHRG AS LBILL,

593 INST_CLM_LN.ALLOWED_CHRG AS LALLOW,

594 INST_CLM_LN.PAYMENT_AMT AS LPAID,

595 INST_CLM_LN.PRE_DSCNT_ALWD_AMT AS LPREDSC,

596 INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT AS LTMAANPD,

597 INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT AS LADJPREDSC,

598 INST_CLM_LN.TMA_OHI_AMT AS LTMAOHI,

17 The SAS System 15:12 Wednesday, March 21, 2018

599 INST_CLM_LN.NO_SERVICES AS LNOSVCS,

600 INST_CLM_LN.REND_PROVIDER AS RENDPROV,

601 INST_CLM_LN.TED_PRVDR_ST_CD AS TEDPRVST,

602 INST_CLM_LN.PROVIDER_ID AS PROVID,

603 INST_CLM_LN.PRVDR_NAME AS BILLNAME,

604 INST_CLM_LN.PROV_ZIP_CODE AS PROVZIP,

605 INST_CLM_LN.PROV_SPECIALTY AS PROVSPEC,

606 INST_CLM_LN.PROV_TYPE AS PROVTYPE,

607 INST_CLM_LN.TMA_ENRLMT_HLTH_CD AS TMAENRCD,

608 INST_CLM_LN.HOSP_PROF_SERV AS HOSOPSVC,

609 INST_CLM_LN.PLAN_CVRG_OPTN_CD AS PLANCOPT,

610 INST_CLM_LN.MCS_SYSTEM_IND AS INOUTREG,

611 INST_CLM_LN.DRVD_MCS_OPTN_IND AS CLMCLS,

612 INST_CLM_LN.TMA_PLACE_TRTMT_CD AS TMAPOS,

613 INST_CLM_LN.HCSR_SPC_PRCS_1_CD AS SSPPROC1,

614 INST_CLM_LN.HCSR_SPC_PRCS_2_CD AS SSPPROC2,

615 INST_CLM_LN.HCSR_SPC_PRCS_3_CD AS SSPPROC3,

616 INST_CLM_LN.HCSR_SPC_PRCS_4_CD AS SSPPROC4,

617 INST_CLM_LN.PRVDR_TXMY_CD AS PROVTXMY,

618 INST_CLM_HDR.CLM_BASE_ID AS CLMNBR,

619 INST_CLM_HDR.CHAP_CLM_SUB_NO AS SUBNBR,

620 INST_CLM_HDR.CLM_ADJ_ID AS CLMADJ,

621 INST_CLM_HDR.CLM_TRANS_TYP AS TRANSTYP,

622 INST_CLM_HDR.CLM_BGN_SRVC_DT AS HBEGDT,

623 INST_CLM_HDR.CLM_END_SRVC_DT AS HENDDT,

624 INST_CLM_HDR.DRG_DIAG1_CD AS DIAG1,

625 INST_CLM_HDR.SPON_STATUS AS SPSTAT,

626 INST_CLM_HDR.PATNT_SEX_REL_CODE AS PTSXREL,

627 INST_CLM_HDR.SETTLE_CUTOFF_DATE AS DTPROC,

628 INST_CLM_HDR.ADMIT_SRVC_DT AS ADMITDT,

629 INST_CLM_HDR.PROV_TOT_CHRG AS HBILL,

630 INST_CLM_HDR.ALLOWED_CHRG AS HALLOW,

631 INST_CLM_HDR.PAYMENT_AMT AS HPAID,

632 INST_CLM_HDR.OTH_CARR_PAY AS HOHIPAID,

633 INST_CLM_HDR.PRE_DSCNT_ALWD_AMT AS HPREDSC,

634 INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT AS LPBADJPREDSC,

635 INST_CLM_HDR.DEBIT_CREDIT_CD AS HDEBITCREDIT,

636 INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS HCREDADJCD,

637 INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT AS HTMAANPD,

638 INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT AS HADJPREDSC,

639 INST_CLM_HDR.ROOM_BRD_IND AS ROOMBRD,

640 INST_CLM_HDR.PATNT_STATE AS BENEST,

641 INST_CLM_HDR.PATNT_ZIP_CODE AS BENEZIP,

642 INST_CLM_HDR.T3_CNTRCT_IND AS T3IND

643

644 FROM

645 INST_CLM_LN INNER JOIN INST_CLM_HDR ON (INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID

646 and INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID and

647 INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP and

648 INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO)

649

650 WHERE (SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3)

651 AND

652 INST_CLM_HDR.CLM_ADJ_ID = 'ALL'

653

654 (SELECT MIN (HD2.CLM_ADJ_ID)

655 FROM INST_CLM_HDR HD2

656 WHERE

18 The SAS System 15:12 Wednesday, March 21, 2018

657 HDLV.CLM_BASE_ID = HD2.CLM_BASE_ID AND

658 HDLV.CHAP_CLM_SUB_NO = HD2.CHAP_CLM_SUB_NO AND

659 HD2.SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3

660 )

661

662 );

ERROR: Teradata prepare: Syntax error: expected something between '(' and the 'SELECT' keyword. SQL statement was: SELECT

INST_CLM_LN.CLM_LINE_ID AS CLMLN, INST_CLM_LN.CLM_BGN_SRVC_DT AS LBEGDT, INST_CLM_LN.CLM_END_SRVC_DT AS LENDDT,

INST_CLM_LN.MCS_AT_RISK AS RISKIND, INST_CLM_LN.PROV_TOT_CHRG AS LBILL, INST_CLM_LN.ALLOWED_CHRG AS LALLOW,

INST_CLM_LN.PAYMENT_AMT AS LPAID, INST_CLM_LN.PRE_DSCNT_ALWD_AMT AS LPREDSC, INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT AS

LTMAANPD, INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT AS LADJPREDSC, INST_CLM_LN.TMA_OHI_AMT AS LTMAOHI, INST_CLM_LN.NO_SERVICES AS

LNOSVCS, INST_CLM_LN.REND_PROVIDER AS RENDPROV, INST_CLM_LN.TED_PRVDR_ST_CD AS TEDPRVST, INST_CLM_LN.PROVIDER_ID AS PROVID,

INST_CLM_LN.PRVDR_NAME AS BILLNAME, INST_CLM_LN.PROV_ZIP_CODE AS PROVZIP, INST_CLM_LN.PROV_SPECIALTY AS PROVSPEC,

INST_CLM_LN.PROV_TYPE AS PROVTYPE, INST_CLM_LN.TMA_ENRLMT_HLTH_CD AS TMAENRCD, INST_CLM_LN.HOSP_PROF_SERV AS HOSOPSVC,

INST_CLM_LN.PLAN_CVRG_OPTN_CD AS PLANCOPT, INST_CLM_LN.MCS_SYSTEM_IND AS INOUTREG, INST_CLM_LN.DRVD_MCS_OPTN_IND AS CLMCLS,

INST_CLM_LN.TMA_PLACE_TRTMT_CD AS TMAPOS, INST_CLM_LN.HCSR_SPC_PRCS_1_CD AS SSPPROC1, INST_CLM_LN.HCSR_SPC_PRCS_2_CD AS

SSPPROC2, INST_CLM_LN.HCSR_SPC_PRCS_3_CD AS SSPPROC3, INST_CLM_LN.HCSR_SPC_PRCS_4_CD AS SSPPROC4, INST_CLM_LN.PRVDR_TXMY_CD

AS PROVTXMY, INST_CLM_HDR.CLM_BASE_ID AS CLMNBR, INST_CLM_HDR.CHAP_CLM_SUB_NO AS SUBNBR, INST_CLM_HDR.CLM_ADJ_ID AS CLMADJ,

INST_CLM_HDR.CLM_TRANS_TYP AS TRANSTYP, INST_CLM_HDR.CLM_BGN_SRVC_DT AS HBEGDT, INST_CLM_HDR.CLM_END_SRVC_DT AS HENDDT,

INST_CLM_HDR.DRG_DIAG1_CD AS DIAG1, INST_CLM_HDR.SPON_STATUS AS SPSTAT, INST_CLM_HDR.PATNT_SEX_REL_CODE AS PTSXREL,

INST_CLM_HDR.SETTLE_CUTOFF_DATE AS DTPROC, INST_CLM_HDR.ADMIT_SRVC_DT AS ADMITDT, INST_CLM_HDR.PROV_TOT_CHRG AS HBILL,

INST_CLM_HDR.ALLOWED_CHRG AS HALLOW, INST_CLM_HDR.PAYMENT_AMT AS HPAID, INST_CLM_HDR.OTH_CARR_PAY AS HOHIPAID,

INST_CLM_HDR.PRE_DSCNT_ALWD_AMT AS HPREDSC, INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT AS LPBADJPREDSC,

INST_CLM_HDR.DEBIT_CREDIT_CD AS HDEBITCREDIT, INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS HCREDADJCD,

INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT AS HTMAANPD, INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT AS HADJPREDSC,

INST_CLM_HDR.ROOM_BRD_IND AS ROOMBRD, INST_CLM_HDR.PATNT_STATE AS BENEST, INST_CLM_HDR.PATNT_ZIP_CODE AS BENEZIP,

INST_CLM_HDR.T3_CNTRCT_IND AS T3IND FROM INST_CLM_LN INNER JOIN INST_CLM_HDR ON

(INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID and INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID and

INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP and INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO) WHERE

(SETTLE_CUTOFF_DATE BETWEEN '2018-01-01' AND '2018-01-31') AND INST_CLM_HDR.CLM_ADJ_ID = 'ALL' (SELECT MIN (HD2.CLM_ADJ_ID)

FROM INST_CLM_HDR HD2 WHERE HDLV.CLM_BASE_ID = HD2.CLM_BASE_ID AND HDLV.CHAP_CLM_SUB_NO = HD2.CHAP_CLM_SUB_NO AND

HD2.SETTLE_CUTOFF_DATE BETWEEN '2018-01-01' AND '2018-01-31' ).

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

663

664

665 %PUT &SQLXMSG &SQLXRC;

Super User
Posts: 6,921

Re: Where to put parenthesis per the error

It looks like you are beginning a new SELECT statement (log line 654) before finishing the old one.  What is your intention here?

Frequent Contributor
Posts: 135

Re: Where to put parenthesis per the error

Posted in reply to Astounding

I'm trying to get the minimum of the claim adjust id. So I was told to do (SELECT MIN (CLM_ADJ_ID) ...

Super User
Posts: 8,214

Re: Where to put parenthesis per the error

Sounds like you only needed to add:

min(INST_CLM_HDR.CLM_ADJ_ID)

 

in your initial select statement

 

along with a group by CLMLN statement

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 135

Re: Where to put parenthesis per the error

I tried that at line 620 and I got an error:

 

583 proc sql ;

584 CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");

585 CREATE TABLE &NAME..CLMEXZ01 AS

586 SELECT * FROM CONNECTION TO tera

587 (SELECT

588 INST_CLM_LN.CLM_LINE_ID AS CLMLN,

589 INST_CLM_LN.CLM_BGN_SRVC_DT AS LBEGDT,

590 INST_CLM_LN.CLM_END_SRVC_DT AS LENDDT,

591 INST_CLM_LN.MCS_AT_RISK AS RISKIND,

592 INST_CLM_LN.PROV_TOT_CHRG AS LBILL,

593 INST_CLM_LN.ALLOWED_CHRG AS LALLOW,

 

17 The SAS System 15:12 Wednesday, March 21, 2018

594 INST_CLM_LN.PAYMENT_AMT AS LPAID,

595 INST_CLM_LN.PRE_DSCNT_ALWD_AMT AS LPREDSC,

596 INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT AS LTMAANPD,

597 INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT AS LADJPREDSC,

598 INST_CLM_LN.TMA_OHI_AMT AS LTMAOHI,

599 INST_CLM_LN.NO_SERVICES AS LNOSVCS,

600 INST_CLM_LN.REND_PROVIDER AS RENDPROV,

601 INST_CLM_LN.TED_PRVDR_ST_CD AS TEDPRVST,

602 INST_CLM_LN.PROVIDER_ID AS PROVID,

603 INST_CLM_LN.PRVDR_NAME AS BILLNAME,

604 INST_CLM_LN.PROV_ZIP_CODE AS PROVZIP,

605 INST_CLM_LN.PROV_SPECIALTY AS PROVSPEC,

606 INST_CLM_LN.PROV_TYPE AS PROVTYPE,

607 INST_CLM_LN.TMA_ENRLMT_HLTH_CD AS TMAENRCD,

608 INST_CLM_LN.HOSP_PROF_SERV AS HOSOPSVC,

609 INST_CLM_LN.PLAN_CVRG_OPTN_CD AS PLANCOPT,

610 INST_CLM_LN.MCS_SYSTEM_IND AS INOUTREG,

611 INST_CLM_LN.DRVD_MCS_OPTN_IND AS CLMCLS,

612 INST_CLM_LN.TMA_PLACE_TRTMT_CD AS TMAPOS,

613 INST_CLM_LN.HCSR_SPC_PRCS_1_CD AS SSPPROC1,

614 INST_CLM_LN.HCSR_SPC_PRCS_2_CD AS SSPPROC2,

615 INST_CLM_LN.HCSR_SPC_PRCS_3_CD AS SSPPROC3,

616 INST_CLM_LN.HCSR_SPC_PRCS_4_CD AS SSPPROC4,

617 INST_CLM_LN.PRVDR_TXMY_CD AS PROVTXMY,

618 INST_CLM_HDR.CLM_BASE_ID AS CLMNBR,

619 INST_CLM_HDR.CHAP_CLM_SUB_NO AS SUBNBR,

620 min(INST_CLM_HDR.CLM_ADJ_ID) AS CLMADJ,

621 INST_CLM_HDR.CLM_TRANS_TYP AS TRANSTYP,

622 INST_CLM_HDR.CLM_BGN_SRVC_DT AS HBEGDT,

623 INST_CLM_HDR.CLM_END_SRVC_DT AS HENDDT,

624 INST_CLM_HDR.DRG_DIAG1_CD AS DIAG1,

625 INST_CLM_HDR.SPON_STATUS AS SPSTAT,

626 INST_CLM_HDR.PATNT_SEX_REL_CODE AS PTSXREL,

627 INST_CLM_HDR.SETTLE_CUTOFF_DATE AS DTPROC,

628 INST_CLM_HDR.ADMIT_SRVC_DT AS ADMITDT,

629 INST_CLM_HDR.PROV_TOT_CHRG AS HBILL,

630 INST_CLM_HDR.ALLOWED_CHRG AS HALLOW,

631 INST_CLM_HDR.PAYMENT_AMT AS HPAID,

632 INST_CLM_HDR.OTH_CARR_PAY AS HOHIPAID,

633 INST_CLM_HDR.PRE_DSCNT_ALWD_AMT AS HPREDSC,

634 INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT AS LPBADJPREDSC,

635 INST_CLM_HDR.DEBIT_CREDIT_CD AS HDEBITCREDIT,

636 INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS HCREDADJCD,

637 INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT AS HTMAANPD,

638 INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT AS HADJPREDSC,

639 INST_CLM_HDR.ROOM_BRD_IND AS ROOMBRD,

640 INST_CLM_HDR.PATNT_STATE AS BENEST,

641 INST_CLM_HDR.PATNT_ZIP_CODE AS BENEZIP,

642 INST_CLM_HDR.T3_CNTRCT_IND AS T3IND

643

644 FROM

645 INST_CLM_LN INNER JOIN INST_CLM_HDR ON (INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID

646 and INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID and

647 INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP and

648 INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO)

649

650 WHERE (SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3)

651 AND

18 The SAS System 15:12 Wednesday, March 21, 2018

652 INST_CLM_HDR.CLM_ADJ_ID = 'ALL'

653

654 /*MIN(HD2.CLM_ADJ_ID)

655 FROM INST_CLM_HDR HD2

656 WHERE

657 HDLV.CLM_BASE_ID = HD2.CLM_BASE_ID AND

658 HDLV.CHAP_CLM_SUB_NO = HD2.CHAP_CLM_SUB_NO AND

659 HD2.SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3*/

660 )

661

662 );

_

22

200

ERROR 22-322: Syntax error, expecting one of the following: a name, ;, ',', ANSIMISS, AS, CROSS, EXCEPT, FULL, GROUP, HAVING,

INNER, INTERSECT, JOIN, LEFT, NATURAL, NOMISS, 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.

663

664

665 %PUT &SQLXMSG &SQLXRC;

0

667

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

NOTE: PROCEDURE SQL used (Total process time):

real time 0.08 seconds

cpu time 0.01 seconds

 

 

Super User
Posts: 8,214

Re: Where to put parenthesis per the error

try changing WHERE to HAVING

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 135

Re: Where to put parenthesis per the error

HAVING didn't work.  There seems to be a punctuation error, I've tried a thousand different ways and still get an error:

 

 

proc sql ;

584 CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");

585 CREATE TABLE &NAME..CLMEXZ01 AS

586 SELECT * FROM CONNECTION TO tera

587 (SELECT

588 INST_CLM_LN.CLM_LINE_ID AS CLMLN,

589 INST_CLM_LN.CLM_BGN_SRVC_DT AS LBEGDT,

590 INST_CLM_LN.CLM_END_SRVC_DT AS LENDDT,

591 INST_CLM_LN.MCS_AT_RISK AS RISKIND,

592 INST_CLM_LN.PROV_TOT_CHRG AS LBILL,

593 INST_CLM_LN.ALLOWED_CHRG AS LALLOW,

17 The SAS System 21:00 Wednesday, March 21, 2018

594 INST_CLM_LN.PAYMENT_AMT AS LPAID,

595 INST_CLM_LN.PRE_DSCNT_ALWD_AMT AS LPREDSC,

596 INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT AS LTMAANPD,

597 INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT AS LADJPREDSC,

598 INST_CLM_LN.TMA_OHI_AMT AS LTMAOHI,

599 INST_CLM_LN.NO_SERVICES AS LNOSVCS,

600 INST_CLM_LN.REND_PROVIDER AS RENDPROV,

601 INST_CLM_LN.TED_PRVDR_ST_CD AS TEDPRVST,

602 INST_CLM_LN.PROVIDER_ID AS PROVID,

603 INST_CLM_LN.PRVDR_NAME AS BILLNAME,

604 INST_CLM_LN.PROV_ZIP_CODE AS PROVZIP,

605 INST_CLM_LN.PROV_SPECIALTY AS PROVSPEC,

606 INST_CLM_LN.PROV_TYPE AS PROVTYPE,

607 INST_CLM_LN.TMA_ENRLMT_HLTH_CD AS TMAENRCD,

608 INST_CLM_LN.HOSP_PROF_SERV AS HOSOPSVC,

609 INST_CLM_LN.PLAN_CVRG_OPTN_CD AS PLANCOPT,

610 INST_CLM_LN.MCS_SYSTEM_IND AS INOUTREG,

611 INST_CLM_LN.DRVD_MCS_OPTN_IND AS CLMCLS,

612 INST_CLM_LN.TMA_PLACE_TRTMT_CD AS TMAPOS,

613 INST_CLM_LN.HCSR_SPC_PRCS_1_CD AS SSPPROC1,

614 INST_CLM_LN.HCSR_SPC_PRCS_2_CD AS SSPPROC2,

615 INST_CLM_LN.HCSR_SPC_PRCS_3_CD AS SSPPROC3,

616 INST_CLM_LN.HCSR_SPC_PRCS_4_CD AS SSPPROC4,

617 INST_CLM_LN.PRVDR_TXMY_CD AS PROVTXMY,

618 INST_CLM_HDR.CLM_BASE_ID AS CLMNBR,

619 INST_CLM_HDR.CHAP_CLM_SUB_NO AS SUBNBR,

620 INST_CLM_HDR.CLM_ADJ_ID AS CLMADJ,

621 INST_CLM_HDR.CLM_TRANS_TYP AS TRANSTYP,

622 INST_CLM_HDR.CLM_BGN_SRVC_DT AS HBEGDT,

623 INST_CLM_HDR.CLM_END_SRVC_DT AS HENDDT,

624 INST_CLM_HDR.DRG_DIAG1_CD AS DIAG1,

625 INST_CLM_HDR.SPON_STATUS AS SPSTAT,

626 INST_CLM_HDR.PATNT_SEX_REL_CODE AS PTSXREL,

627 INST_CLM_HDR.SETTLE_CUTOFF_DATE AS DTPROC,

628 INST_CLM_HDR.ADMIT_SRVC_DT AS ADMITDT,

629 INST_CLM_HDR.PROV_TOT_CHRG AS HBILL,

630 INST_CLM_HDR.ALLOWED_CHRG AS HALLOW,

631 INST_CLM_HDR.PAYMENT_AMT AS HPAID,

632 INST_CLM_HDR.OTH_CARR_PAY AS HOHIPAID,

633 INST_CLM_HDR.PRE_DSCNT_ALWD_AMT AS HPREDSC,

634 INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT AS LPBADJPREDSC,

635 INST_CLM_HDR.DEBIT_CREDIT_CD AS HDEBITCREDIT,

636 INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS HCREDADJCD,

637 INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT AS HTMAANPD,

638 INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT AS HADJPREDSC,

639 INST_CLM_HDR.ROOM_BRD_IND AS ROOMBRD,

640 INST_CLM_HDR.PATNT_STATE AS BENEST,

641 INST_CLM_HDR.PATNT_ZIP_CODE AS BENEZIP,

642 INST_CLM_HDR.T3_CNTRCT_IND AS T3IND

643

644 FROM

645 INST_CLM_LN INNER JOIN INST_CLM_HDR

646 ON

647 INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID

648 AND INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID AND

649 INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP AND

650 INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO

651

18 The SAS System 21:00 Wednesday, March 21, 2018

652 WHERE INST_CLM_HDR.SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3

653 AND

654 INST_CLM_HDR.CLM_ADJ_ID = 'ALL'

655

656 SELECT MIN(HD2.CLM_ADJ_ID)

657 FROM INST_CLM_HDR HD2

658 WHERE

659 INST_CLM_HDR.CLM_BASE_ID = HD2.CLM_BASE_ID AND

660 INST_CLM_HDR.CHAP_CLM_SUB_NO = HD2.CHAP_CLM_SUB_NO AND

661 HD2.SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3

662 );

ERROR: Teradata prepare: Syntax error, expected something like ';' between a string or a Unicode character literal and the 'SELECT'

keyword. SQL statement was: SELECT INST_CLM_LN.CLM_LINE_ID AS CLMLN, INST_CLM_LN.CLM_BGN_SRVC_DT AS LBEGDT,

INST_CLM_LN.CLM_END_SRVC_DT AS LENDDT, INST_CLM_LN.MCS_AT_RISK AS RISKIND, INST_CLM_LN.PROV_TOT_CHRG AS LBILL,

INST_CLM_LN.ALLOWED_CHRG AS LALLOW, INST_CLM_LN.PAYMENT_AMT AS LPAID, INST_CLM_LN.PRE_DSCNT_ALWD_AMT AS LPREDSC,

INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT AS LTMAANPD, INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT AS LADJPREDSC, INST_CLM_LN.TMA_OHI_AMT

AS LTMAOHI, INST_CLM_LN.NO_SERVICES AS LNOSVCS, INST_CLM_LN.REND_PROVIDER AS RENDPROV, INST_CLM_LN.TED_PRVDR_ST_CD AS

TEDPRVST, INST_CLM_LN.PROVIDER_ID AS PROVID, INST_CLM_LN.PRVDR_NAME AS BILLNAME, INST_CLM_LN.PROV_ZIP_CODE AS PROVZIP,

INST_CLM_LN.PROV_SPECIALTY AS PROVSPEC, INST_CLM_LN.PROV_TYPE AS PROVTYPE, INST_CLM_LN.TMA_ENRLMT_HLTH_CD AS TMAENRCD,

INST_CLM_LN.HOSP_PROF_SERV AS HOSOPSVC, INST_CLM_LN.PLAN_CVRG_OPTN_CD AS PLANCOPT, INST_CLM_LN.MCS_SYSTEM_IND AS INOUTREG,

INST_CLM_LN.DRVD_MCS_OPTN_IND AS CLMCLS, INST_CLM_LN.TMA_PLACE_TRTMT_CD AS TMAPOS, INST_CLM_LN.HCSR_SPC_PRCS_1_CD AS

SSPPROC1, INST_CLM_LN.HCSR_SPC_PRCS_2_CD AS SSPPROC2, INST_CLM_LN.HCSR_SPC_PRCS_3_CD AS SSPPROC3,

INST_CLM_LN.HCSR_SPC_PRCS_4_CD AS SSPPROC4, INST_CLM_LN.PRVDR_TXMY_CD AS PROVTXMY, INST_CLM_HDR.CLM_BASE_ID AS CLMNBR,

INST_CLM_HDR.CHAP_CLM_SUB_NO AS SUBNBR, INST_CLM_HDR.CLM_ADJ_ID AS CLMADJ, INST_CLM_HDR.CLM_TRANS_TYP AS TRANSTYP,

INST_CLM_HDR.CLM_BGN_SRVC_DT AS HBEGDT, INST_CLM_HDR.CLM_END_SRVC_DT AS HENDDT, INST_CLM_HDR.DRG_DIAG1_CD AS DIAG1,

INST_CLM_HDR.SPON_STATUS AS SPSTAT, INST_CLM_HDR.PATNT_SEX_REL_CODE AS PTSXREL, INST_CLM_HDR.SETTLE_CUTOFF_DATE AS DTPROC,

INST_CLM_HDR.ADMIT_SRVC_DT AS ADMITDT, INST_CLM_HDR.PROV_TOT_CHRG AS HBILL, INST_CLM_HDR.ALLOWED_CHRG AS HALLOW,

INST_CLM_HDR.PAYMENT_AMT AS HPAID, INST_CLM_HDR.OTH_CARR_PAY AS HOHIPAID, INST_CLM_HDR.PRE_DSCNT_ALWD_AMT AS HPREDSC,

INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT AS LPBADJPREDSC, INST_CLM_HDR.DEBIT_CREDIT_CD AS HDEBITCREDIT,

INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS HCREDADJCD, INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT AS HTMAANPD,

INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT AS HADJPREDSC, INST_CLM_HDR.ROOM_BRD_IND AS ROOMBRD, INST_CLM_HDR.PATNT_STATE AS BENEST,

INST_CLM_HDR.PATNT_ZIP_CODE AS BENEZIP, INST_CLM_HDR.T3_CNTRCT_IND AS T3IND FROM INST_CLM_LN INNER JOIN INST_CLM_HDR ON

INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID AND INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID AND

INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP AND INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO WHERE

INST_CLM_HDR.SETTLE_CUTOFF_DATE BETWEEN '2018-01-01' AND '2018-01-31' AND INST_CLM_HDR.CLM_ADJ_ID = 'ALL' SELECT

MIN(HD2.CLM_ADJ_ID) FROM INST_CLM_HDR HD2 WHERE INST_CLM_HDR.CLM_BASE_ID = HD2.CLM_BASE_ID AND INST_CLM_HDR.CHAP_CLM_SUB_NO

= HD2.CHAP_CLM_SUB_NO AND HD2.SETTLE_CUTOFF_DATE BETWEEN '2018-01-01' AND '2018-01-31'.

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

663 %PUT &SQLXMSG &SQLXRC;

Syntax error, expected something like ';' between a string or a Unicode character literal and the 'SELECT' keyword 3707

664

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

NOTE: PROCEDURE SQL used (Total process time):

real time 0.08 seconds

cpu time 0.01 seconds

 

Super User
Posts: 8,214

Re: Where to put parenthesis per the error

Post the code (not the log) that does run (without error) that you were originally trying to modify. It should be easy to add the one statement you were trying to add.

 

Art, CEO, AnalystFinder..com

 

Frequent Contributor
Posts: 135

Re: Where to put parenthesis per the error

trying to change the last steps:        WHERE (SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3)          
                     AND                                                       
                     INST_CLM_LN.CLM_ADJ_ID = '099'                                     
                     AND                                                       
                     INST_CLM_HDR.T3_CNTRCT_IND = 'T7'                                   
        );                                                                      

 

here is all of it:

 

  %LET DATE1 = '2018-01-01' ; /*- SETTLEMENT START DATE -*/                   
    %LET DATE3 = '2018-01-31' ; /*- SETTLEMENT THRU DATE -*/                    
                                                                               
  proc sql ;
   CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");
   CREATE TABLE &NAME..CLMEXZ01 AS
        SELECT * FROM CONNECTION TO tera                     
           (SELECT                                                             
               INST_CLM_LN.CLM_LINE_ID              AS     CLMLN,                       
               INST_CLM_LN.CLM_BGN_SRVC_DT          AS     LBEGDT,                      
               INST_CLM_LN.CLM_END_SRVC_DT          AS     LENDDT,                      
               INST_CLM_LN.MCS_AT_RISK              AS     RISKIND,                     
               INST_CLM_LN.PROV_TOT_CHRG            AS     LBILL,                       
               INST_CLM_LN.ALLOWED_CHRG             AS     LALLOW,                      
               INST_CLM_LN.PAYMENT_AMT              AS     LPAID,                       
               INST_CLM_LN.PRE_DSCNT_ALWD_AMT       AS     LPREDSC,                     
               INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT  AS     LTMAANPD,                    
               INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT   AS     LADJPREDSC,                  
               INST_CLM_LN.TMA_OHI_AMT              AS     LTMAOHI,                     
               INST_CLM_LN.NO_SERVICES              AS     LNOSVCS,                     
               INST_CLM_LN.REND_PROVIDER            AS     RENDPROV,                    
               INST_CLM_LN.TED_PRVDR_ST_CD          AS     TEDPRVST,                    
               INST_CLM_LN.PROVIDER_ID              AS     PROVID,                      
               INST_CLM_LN.PRVDR_NAME               AS     BILLNAME,                    
               INST_CLM_LN.PROV_ZIP_CODE            AS     PROVZIP,                     
               INST_CLM_LN.PROV_SPECIALTY           AS     PROVSPEC,                    
               INST_CLM_LN.PROV_TYPE                AS     PROVTYPE,                    
               INST_CLM_LN.TMA_ENRLMT_HLTH_CD       AS     TMAENRCD,                    
               INST_CLM_LN.HOSP_PROF_SERV           AS     HOSOPSVC,                    
               INST_CLM_LN.PLAN_CVRG_OPTN_CD        AS     PLANCOPT,                    
               INST_CLM_LN.MCS_SYSTEM_IND           AS     INOUTREG,                    
               INST_CLM_LN.DRVD_MCS_OPTN_IND        AS     CLMCLS,                      
               INST_CLM_LN.TMA_PLACE_TRTMT_CD       AS     TMAPOS,                      
               INST_CLM_LN.HCSR_SPC_PRCS_1_CD       AS     SSPPROC1,                    
               INST_CLM_LN.HCSR_SPC_PRCS_2_CD       AS     SSPPROC2,                    
               INST_CLM_LN.HCSR_SPC_PRCS_3_CD       AS     SSPPROC3,                    
               INST_CLM_LN.HCSR_SPC_PRCS_4_CD       AS     SSPPROC4,                    
               INST_CLM_LN.PRVDR_TXMY_CD            AS     PROVTXMY,                    
               INST_CLM_HDR.CLM_BASE_ID              AS     CLMNBR,                      
               INST_CLM_HDR.CHAP_CLM_SUB_NO          AS     SUBNBR,                      
               INST_CLM_HDR.CLM_ADJ_ID               AS     CLMADJ,                      
               INST_CLM_HDR.CLM_TRANS_TYP            AS     TRANSTYP,                    
               INST_CLM_HDR.CLM_BGN_SRVC_DT          AS     HBEGDT,                      
               INST_CLM_HDR.CLM_END_SRVC_DT          AS     HENDDT,                      
               INST_CLM_HDR.DRG_DIAG1_CD             AS     DIAG1,                       
               INST_CLM_HDR.SPON_STATUS              AS     SPSTAT,                      
               INST_CLM_HDR.PATNT_SEX_REL_CODE       AS     PTSXREL,                     
               INST_CLM_HDR.SETTLE_CUTOFF_DATE       AS     DTPROC,                     
               INST_CLM_HDR.ADMIT_SRVC_DT            AS     ADMITDT,                     
               INST_CLM_HDR.PROV_TOT_CHRG            AS     HBILL,                       
               INST_CLM_HDR.ALLOWED_CHRG             AS     HALLOW,                      
               INST_CLM_HDR.PAYMENT_AMT              AS     HPAID,                       
               INST_CLM_HDR.OTH_CARR_PAY             AS     HOHIPAID,                    
               INST_CLM_HDR.PRE_DSCNT_ALWD_AMT       AS     HPREDSC,                     
               INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT  AS    LPBADJPREDSC,                
               INST_CLM_HDR.DEBIT_CREDIT_CD           AS     HDEBITCREDIT,                
               INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS    HCREDADJCD,                  
               INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT  AS     HTMAANPD,                    
               INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT   AS     HADJPREDSC,                  
               INST_CLM_HDR.ROOM_BRD_IND             AS     ROOMBRD,                     
               INST_CLM_HDR.PATNT_STATE              AS     BENEST,                      
               INST_CLM_HDR.PATNT_ZIP_CODE           AS     BENEZIP,                     
               INST_CLM_HDR.T3_CNTRCT_IND            AS     T3IND                        
                                                                               
                        FROM
      INST_CLM_LN INNER JOIN INST_CLM_HDR ON (INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID
   and INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID and
   INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP and
   INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO)
                                                                                                         
              WHERE (SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3)          
                     AND                                                       
                     INST_CLM_LN.CLM_ADJ_ID = '099'                                     
                     AND                                                       
                     INST_CLM_HDR.T3_CNTRCT_IND = 'T7'                                   
        );                                                                     
                                                                               
  %PUT &SQLXMSG &SQLXRC;                                                       
                                                                                                                           
                                                                        

Super User
Posts: 8,214

Re: Where to put parenthesis per the error

I don't have teradata thus can't test this but, if that code runs, wouldn't the following do what you want?:

  %LET DATE1 = '2018-01-01' ; /*- SETTLEMENT START DATE -*/                   
    %LET DATE3 = '2018-01-31' ; /*- SETTLEMENT THRU DATE -*/                    
                                                                               
  proc sql ;
   CONNECT TO Teradata as tera (authdomain="T2017_IW_Prod" TDPID="edwfsp.fs.centene.com");
   CREATE TABLE &NAME..CLMEXZ01 AS
        SELECT * FROM CONNECTION TO tera                     
           (SELECT                                                             
               INST_CLM_LN.CLM_LINE_ID              AS     CLMLN,                       
               INST_CLM_LN.CLM_BGN_SRVC_DT          AS     LBEGDT,                      
               INST_CLM_LN.CLM_END_SRVC_DT          AS     LENDDT,                      
               INST_CLM_LN.MCS_AT_RISK              AS     RISKIND,                     
               INST_CLM_LN.PROV_TOT_CHRG            AS     LBILL,                       
               INST_CLM_LN.ALLOWED_CHRG             AS     LALLOW,                      
               INST_CLM_LN.PAYMENT_AMT              AS     LPAID,                       
               INST_CLM_LN.PRE_DSCNT_ALWD_AMT       AS     LPREDSC,                     
               INST_CLM_LN.TMA_NET_PRVDR_DSCNT_AMT  AS     LTMAANPD,                    
               INST_CLM_LN.ADJ_PRE_DSCNT_ALWD_AMT   AS     LADJPREDSC,                  
               INST_CLM_LN.TMA_OHI_AMT              AS     LTMAOHI,                     
               INST_CLM_LN.NO_SERVICES              AS     LNOSVCS,                     
               INST_CLM_LN.REND_PROVIDER            AS     RENDPROV,                    
               INST_CLM_LN.TED_PRVDR_ST_CD          AS     TEDPRVST,                    
               INST_CLM_LN.PROVIDER_ID              AS     PROVID,                      
               INST_CLM_LN.PRVDR_NAME               AS     BILLNAME,                    
               INST_CLM_LN.PROV_ZIP_CODE            AS     PROVZIP,                     
               INST_CLM_LN.PROV_SPECIALTY           AS     PROVSPEC,                    
               INST_CLM_LN.PROV_TYPE                AS     PROVTYPE,                    
               INST_CLM_LN.TMA_ENRLMT_HLTH_CD       AS     TMAENRCD,                    
               INST_CLM_LN.HOSP_PROF_SERV           AS     HOSOPSVC,                    
               INST_CLM_LN.PLAN_CVRG_OPTN_CD        AS     PLANCOPT,                    
               INST_CLM_LN.MCS_SYSTEM_IND           AS     INOUTREG,                    
               INST_CLM_LN.DRVD_MCS_OPTN_IND        AS     CLMCLS,                      
               INST_CLM_LN.TMA_PLACE_TRTMT_CD       AS     TMAPOS,                      
               INST_CLM_LN.HCSR_SPC_PRCS_1_CD       AS     SSPPROC1,                    
               INST_CLM_LN.HCSR_SPC_PRCS_2_CD       AS     SSPPROC2,                    
               INST_CLM_LN.HCSR_SPC_PRCS_3_CD       AS     SSPPROC3,                    
               INST_CLM_LN.HCSR_SPC_PRCS_4_CD       AS     SSPPROC4,                    
               INST_CLM_LN.PRVDR_TXMY_CD            AS     PROVTXMY,                    
               INST_CLM_HDR.CLM_BASE_ID              AS     CLMNBR,                      
               INST_CLM_HDR.CHAP_CLM_SUB_NO          AS     SUBNBR,                      
               INST_CLM_HDR.CLM_ADJ_ID               AS     CLMADJ,                      
               INST_CLM_HDR.CLM_TRANS_TYP            AS     TRANSTYP,                    
               INST_CLM_HDR.CLM_BGN_SRVC_DT          AS     HBEGDT,                      
               INST_CLM_HDR.CLM_END_SRVC_DT          AS     HENDDT,                      
               INST_CLM_HDR.DRG_DIAG1_CD             AS     DIAG1,                       
               INST_CLM_HDR.SPON_STATUS              AS     SPSTAT,                      
               INST_CLM_HDR.PATNT_SEX_REL_CODE       AS     PTSXREL,                     
               INST_CLM_HDR.SETTLE_CUTOFF_DATE       AS     DTPROC,                     
               INST_CLM_HDR.ADMIT_SRVC_DT            AS     ADMITDT,                     
               INST_CLM_HDR.PROV_TOT_CHRG            AS     HBILL,                       
               INST_CLM_HDR.ALLOWED_CHRG             AS     HALLOW,                      
               INST_CLM_HDR.PAYMENT_AMT              AS     HPAID,                       
               INST_CLM_HDR.OTH_CARR_PAY             AS     HOHIPAID,                    
               INST_CLM_HDR.PRE_DSCNT_ALWD_AMT       AS     HPREDSC,                     
               INST_CLM_LN.PB_ADJ_PRE_DSCNT_ALWD_AMT  AS    LPBADJPREDSC,                
               INST_CLM_HDR.DEBIT_CREDIT_CD           AS     HDEBITCREDIT,                
               INST_CLM_HDR.T3_CRED_ADJST_RFND_RSN_CD AS    HCREDADJCD,                  
               INST_CLM_HDR.TMA_NET_PRVDR_DSCNT_AMT  AS     HTMAANPD,                    
               INST_CLM_HDR.ADJ_PRE_DSCNT_ALWD_AMT   AS     HADJPREDSC,                  
               INST_CLM_HDR.ROOM_BRD_IND             AS     ROOMBRD,                     
               INST_CLM_HDR.PATNT_STATE              AS     BENEST,                      
               INST_CLM_HDR.PATNT_ZIP_CODE           AS     BENEZIP,                     
               INST_CLM_HDR.T3_CNTRCT_IND            AS     T3IND,
               min(INST_CLM_HDR.CLM_ADJ_ID) as min_CLM_ADJ_ID                       
                                                                               
                        FROM
      INST_CLM_LN INNER JOIN INST_CLM_HDR ON (INST_CLM_LN.CLM_BASE_ID=INST_CLM_HDR.CLM_BASE_ID
   and INST_CLM_LN.CLM_ADJ_ID=INST_CLM_HDR.CLM_ADJ_ID and
   INST_CLM_LN.CLM_TRANS_TYP=INST_CLM_HDR.CLM_TRANS_TYP and
   INST_CLM_LN.CHAP_CLM_SUB_NO=INST_CLM_HDR.CHAP_CLM_SUB_NO)
                                                                                                         
              WHERE (SETTLE_CUTOFF_DATE BETWEEN &DATE1 AND &DATE3)          
                     AND                                                       
                     INST_CLM_LN.CLM_ADJ_ID = '099'                                     
                     AND                                                       
                     INST_CLM_HDR.T3_CNTRCT_IND = 'T7' 
                 GROUP BY INST_CLM_LN.CLM_ADJ_ID                                  
        );                                                                     
                                                                               
  %PUT &SQLXMSG &SQLXRC;                                                       

Art, CEO, AnalystFinder.com

 

 

 

Frequent Contributor
Posts: 135

Re: Where to put parenthesis per the error

  GROUP BY INST_CLM_LN.CLM_ADJ_ID 

 

Should the above be min(INST_CLM_HDR.CLM_ADJ_ID) ?

 

 

Super User
Posts: 8,214

Re: Where to put parenthesis per the error

I wouldn't think so!

 

The code I suggested simply adds min(INST_CLM_HDR.CLM_ADJ_ID) as a field in your select statement.

 

You've never specified what the minimum should be based on. Yes, GROUP BY INST_CLM_LN.CLM_ADJ_ID is probably grouping by the wrong variable, possibly you want to group by INST_CLM_LN.CLM_LINE_ID, but you are the one who knows what you want the minimum to be based on, however it's not the minimum of the field you are trying to get the minimum of.

 

Art, CEO, AnalystFinder.com

 

Frequent Contributor
Posts: 135

Re: Where to put parenthesis per the error

I see the issue - it's the 'ALL' - it does not work in this program.  Thank you for your help

Ask a Question
Discussion stats
  • 12 replies
  • 200 views
  • 0 likes
  • 3 in conversation