Oracle Prepare Error
in [SAS]
|
Prev: DDE to read Excel file with autofilter turned on?
Next: why simple kappa are different in proc freq order=data, and order=internal
From: Gerhard Hellriegel on 30 Oct 2009 05:13 quite long and my SQL knowledge is quite short... Only a question: in line 161, where does the brace come from in the GROUP BY.. statement? .... 161 ); Gerhard On Thu, 29 Oct 2009 18:09:46 -0400, J Shafiroff <jeff.shafiroff(a)BLUESHIELDCA.COM> wrote: >Hello I am using pass-through code to access Oracle with SQL. I do not >seem to be able to get around this prepare error. Can someone please >help? Thank you. SAS Log file appears below: >________________________________________________________ > >1 The SAS >System 15:05 Thursday, October 29, 2009 > >1 ;*';*";*/;quit;run; >2 OPTIONS PAGENO=MIN; >3 %LET _CLIENTTASKLABEL=%NRBQUOTE(rx_biplr_v3_2); >4 %LET _EGTASKLABEL=%NRBQUOTE(rx_biplr_v3_2); >5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(); >6 %LET _SASPROGRAMFILE=; >7 >8 ODS _ALL_ CLOSE; >NOTE: Some of your options or statements may not be supported with the >Activex or Java series of devices. Graph > defaults for these drivers may be different from other SAS/GRAPH >device drivers. For further information, please > contact Technical Support. >9 OPTIONS DEV=ACTIVEX; >10 FILENAME EGHTML TEMP; >NOTE: Writing HTML(EGHTML) Body file: EGHTML >11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault >11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared% >20Files/BIClientStyles/EGDefault.css") >11 ! ATTRIBUTES= >("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") >NOGTITLE NOGFOOTNOTE >11 ! GPATH=&sasworklocation; >12 >13 %gaccessible; >14 /********************************************************/ >15 * RX_EXTRACT_V3.SAS ; >16 /********************************************************/ >17 >18 dm 'log;clear;out;clear'; >19 options linesize =120; >20 Option obs=0 NoReplace; >21 >22 >************************************************************************** * >*************; >23 *Date Programmer Reviewed By WHAT WAS DONE; >24 >25 >************************************************************************** * >*************; >26 >27 %let begdate='01SEP2009'd; >28 %let enddate='30SEP2009'd; >29 %let q = %str(%') ; >30 >31 > > >32 data _null_ ; >33 call symput('fdos',put(intnx('month',"&begdate"d,- >27,'beginning'), date9.) ) ; >34 call symput('ldos',put(intnx('month',"&enddate"d,- >0,'ending'), date9.) ) ; >35 run ; > >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.01 seconds > > >36 >37 >38 >39 **************************************************************; >40 * ??Use the data _null_ step to create a macro variable for >the ; >41 * year and month based on the ldos macro variable in the data ; >42 * step above?? ; >43 **************************************************************; >44 >45 >46 data _null_ ; >47 if month("&ldos"d) < 10 >48 then call symput('yr_mo',compress(year("&ldos"d) || '0' || >month("&ldos"d) ) ) ; >49 else call symput('yr_mo',compress(year("&ldos"d) || month >("&ldos"d) ) ) ; >50 run ; > >NOTE: Numeric values have been converted to character values at the places >given by: (Line):(Column). > 48:39 48:64 49:39 49:57 >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.00 seconds > > >51 >52 *%put &biplr_dx; >53 %put &begdate ; >'01SEP2009'd >54 %put &enddate ; >'30SEP2009'd >55 %put &fdos ; >01JUN2007 >56 %put &ldos ; >30SEP2009 >57 %put &q ; >' >58 >59 >60 libname biplr'/home/jshafi01/projects/adhoc/biplr_dprsn/data'; >NOTE: Libref BIPLR was successfully assigned as follows: > Engine: V9 > Physical Name: /home/jshafi01/projects/adhoc/biplr_dprsn/data >61 libname rosdwp oracle user="&oracle_user." pass="&oracle_pass." >buffsize=32767 path='rosdwp'; >NOTE: Libref ROSDWP was successfully assigned as follows: > Engine: ORACLE > Physical Name: rosdwp >62 >63 data work.biplr_up_v1; >64 attrib mbr_id length=$26; >65 set biplr.biplr_ubh_med_v1; >66 run; > >NOTE: There were 0 observations read from the data set >BIPLR.BIPLR_UBH_MED_V1. >NOTE: The data set WORK.BIPLR_UP_V1 has 0 observations and 1 variables. >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.01 seconds > > >67 >68 proc contents; >69 run; > >NOTE: PROCEDURE CONTENTS used (Total process time): > real time 0.02 seconds > cpu time 0.02 seconds > >70 >71 proc datasets >72 library = rosdwp nolist; >73 delete biplr_up_v1; >74 quit; > >NOTE: Deleting ROSDWP.BIPLR_UP_V1 (memtype=DATA). >NOTE: PROCEDURE DATASETS used (Total process time): > real time 0.67 seconds > cpu time 0.00 seconds > > >75 >76 proc datasets >77 library=work nolist; >78 copy out = rosdwp move; >79 select biplr_up_v1 ; >80 quit; > >NOTE: Moving WORK.BIPLR_UP_V1 to ROSDWP.BIPLR_UP_V1 (memtype=DATA). >NOTE: SAS variable labels, formats, and lengths are not written to DBMS >tables. >NOTE: There were 0 observations read from the data set WORK.BIPLR_UP_V1. >NOTE: The data set ROSDWP.BIPLR_UP_V1 has 0 observations and 1 variables. >NOTE: PROCEDURE DATASETS used (Total process time): > real time 0.12 seconds > cpu time 0.02 seconds > > >81 >82 >83 >84 >************************************************************************** * >**; >85 >************************************************************************** * >*; >86 >87 >88 proc SQL noprint ; >89 connect to oracle as rosdwp (user="&oracle_user." >password="&oracle_pass" buffsize=32767 path="rosdwp" >89 ! preserve_comments); >90 execute(alter session set nls_date_format = 'ddmonyyyy') >by rosdwp; >91 CREATE table biplr_rx as >92 SELECT >93 mrb_id, >94 pcp_spc, >95 rx_date, >96 brand, >97 generic, >98 ahfs_cd, >99 ahfs_dsc, >100 lbl, >101 strngth, >102 unt, >103 spc_tx_cls_cd, >104 spc_tx_cls_dsc, >105 gcn, >106 rx_num, >107 rx_ingrd, >108 rx_paid, >109 rx_cnt, >110 rx_qty, >111 rx_day >112 FROM connection to rosdwp >113 (SELECT >114 mid.UNIQ_MBR_ID as mrb_id, >115 pd.PRVDR_SPCLTY_DESC as pcp_spc, >116 pcf.LAST_SRVC_DT as rx_date, >117 dd.BRAND_NAME as brand, >118 dd.GNRC_NAME as generic, >119 dd.AHFS_THRPTC_CLS_CD as ahfs_cd, >120 dd.AHFS_THRPTC_CLS_DESC as ahfs_dsc, >121 dd.LABEL_NAME as lbl, >122 dd.STRNGTH_NUM as strngth, >123 dd.STRNGTH_UNIT_DESC as unt, >124 dd.SPECF_THRPTC_CLS_CD as spc_tx_cls_cd, >125 dd.SPECF_THRPTC_CLS_DESC as spc_tx_cls_dsc, >126 dd.GCN_NUM as gcn, >127 pcf.PRSCRPTN_NUM as rx_num, >128 SUM(pcf.INGRDNT_AMT) as rx_ingrd, >129 SUM(pcf.PD_AMT) as rx_paid, >130 SUM(pcf.UNIT_SRVC_CNT) as rx_cnt, >131 SUM(pcf.DSPNSD_QTY) as rx_qty, >132 SUM(pcf.DAY_SUPLY_CNT) as rx_day >133 FROM >134 PHRMCY_CLM_FACT pcf, >135 MBR_ID_DMNSN mid, >136 DRUG_DMNSN dd, >137 PRVDR_DMNSN pd >138 WHERE >139 (biplr_up_v1.mbr_id = MBR_ID_DMNSN.UNIQ_MBR_ID) And >140 (PHRMCY_CLM_FACT.FINL_CLM_KEY = >PHRMCY_FINL_CLM_DMNSN.FINL_CLM_KEY) AND >141 (PHRMCY_CLM_FACT.MBR_KEY = MBR_ID_DMNSN.MBR_KEY) And >142 (PHRMCY_CLM_FACT.BNFT_KEY = MBR_BNFT_DMNSN.BNFT_KEY) And >143 (PHRMCY_CLM_FACT.DRUG_KEY = DRUG_DMNSN.DRUG_KEY) And >144 (PHRMCY_FINL_CLM_DMNSN.CLM_ORGN_CD IN ('M','A')) AND >145 (PHRMCY_CLM_FACT.LAST_SRVC_DT >BETWEEN >&q.&fdos.&q and &q.&ldos.&q) >146 GROUP BY >147 mid.UNIQ_MBR_ID , >148 pd.PRVDR_SPCLTY_DESC , >149 pcf.LAST_SRVC_DT , >150 dd.BRAND_NAME , >151 dd.GNRC_NAME , >152 dd.AHFS_THRPTC_CLS_CD , >153 dd.AHFS_THRPTC_CLS_DESC , >154 dd.LABEL_NAME , >155 dd.STRNGTH_NUM , >156 dd.STRNGTH_UNIT_DESC , >157 dd.SPECF_THRPTC_CLS_CD , >158 dd.SPECF_THRPTC_CLS_DESC , >159 dd.GCN_NUM , >160 pcf.PRSCRPTN_NUM >161 ); >ERROR: ORACLE prepare error: ORA-00936: missing expression. SQL statement: >SELECT mid.UNIQ_MBR_ID as mrb_id, > pd.PRVDR_SPCLTY_DESC as pcp_spc, pcf.LAST_SRVC_DT as rx_date, >dd.BRAND_NAME as brand, dd.GNRC_NAME as generic, > dd.AHFS_THRPTC_CLS_CD as ahfs_cd, dd.AHFS_THRPTC_CLS_DESC as >ahfs_dsc, dd.LABEL_NAME as lbl, dd.STRNGTH_NUM as > strngth, dd.STRNGTH_UNIT_DESC as unt, dd.SPECF_THRPTC_CLS_CD as >spc_tx_cls_cd, dd.SPECF_THRPTC_CLS_DESC as > spc_tx_cls_dsc, dd.GCN_NUM as gcn, pcf.PRSCRPTN_NUM as rx_num, SUM >(pcf.INGRDNT_AMT) as rx_ingrd, SUM(pcf.PD_AMT) > as rx_paid, SUM(pcf.UNIT_SRVC_CNT) as rx_cnt, SUM(pcf.DSPNSD_QTY) >as rx_qty, SUM(pcf.DAY_SUPLY_CNT) as rx_day > FROM PHRMCY_CLM_FACT pcf, MBR_ID_DMNSN mid, DRUG_DMNSN dd, >PRVDR_DMNSN pd WHERE (biplr_up_v1.mbr_id = > MBR_ID_DMNSN.UNIQ_MBR_ID) And (PHRMCY_CLM_FACT.FINL_CLM_KEY = >PHRMCY_FINL_CLM_DMNSN.FINL_CLM_KEY) AND > (PHRMCY_CLM_FACT.MBR_KEY = MBR_ID_DMNSN.MBR_KEY) And >(PHRMCY_CLM_FACT.BNFT_KEY = MBR_BNFT_DMNSN.BNFT_KEY) And > (PHRMCY_CLM_FACT.DRUG_KEY = DRUG_DMNSN.DRUG_KEY) And >(PHRMCY_FINL_CLM_DMNSN.CLM_ORGN_CD IN ('M','A')) AND > (PHRMCY_CLM_FACT.LAST_SRVC_DT BETWEEN between '01JUN2007' >and '30SEP2009') GROUP BY mid.UNIQ_MBR_ID , > pd.PRVDR_SPCLTY_DESC , pcf.LAST_SRVC_DT , dd.BRAND_NAME , >dd.GNRC_NAME , dd.AHFS_THRPTC_CLS_CD , > dd.AHFS_THRPTC_CLS_DESC , dd.LABEL_NAME , dd.STRNGTH_NUM , >dd.STRNGTH_UNIT_DESC , dd.SPECF_THRPTC_CLS_CD , > dd.SPECF_THRPTC_CLS_DESC , dd.GCN_NUM , pcf.PRSCRPTN_NUM. >NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of >statements. >162 execute (drop table biplr_up_v1) by rosdwp; >NOTE: Statement not executed due to NOEXEC option. >163 disconnect from rosdwp; >NOTE: Statement not executed due to NOEXEC option. >164 quit; >NOTE: The SAS System stopped processing this step because of errors. >NOTE: PROCEDURE SQL used (Total process time): > real time 0.12 seconds > cpu time 0.00 seconds > >165 >166 >167 >168 >169 %LET _CLIENTTASKLABEL=; >170 %LET _EGTASKLABEL=; >171 %LET _CLIENTPROJECTNAME=; >172 %LET _SASPROGRAMFILE=; >173 >174 ;*';*";*/;quit;run; >175 ODS _ALL_ CLOSE; >176 >177 >178 QUIT; RUN; >179
From: Fernández RodrÃguez, on 30 Oct 2009 07:45 Hi, I think you have to code the sintaxis like this (UPPERCASE is what I think you are missing): proc sql; connect to oracle as rosdwp (user=*** password=*** path=***); create table Newdataset as SELECT * FROM CONNECTION TO ROSDWP ( select mrb_id, pcp_spc, rx_date, ...... ...... from ... where .... group by .... ) ; disconnect from rosdwp; quit; Daniel Fernandez. Barcelona -----Mensaje original----- De: SAS(r) Discussion [mailto:SAS-L(a)LISTSERV.UGA.EDU] En nombre de Gerhard Hellriegel Enviado el: divendres, 30 / octubre / 2009 10:13 Para: SAS-L(a)LISTSERV.UGA.EDU Asunto: Re: Oracle Prepare Error quite long and my SQL knowledge is quite short... Only a question: in line 161, where does the brace come from in the GROUP BY.. statement? .... 161 ); Gerhard On Thu, 29 Oct 2009 18:09:46 -0400, J Shafiroff <jeff.shafiroff(a)BLUESHIELDCA.COM> wrote: >Hello I am using pass-through code to access Oracle with SQL. I do not >seem to be able to get around this prepare error. Can someone please >help? Thank you. SAS Log file appears below: >________________________________________________________ > >1 The SAS >System 15:05 Thursday, October 29, 2009 > >1 ;*';*";*/;quit;run; >2 OPTIONS PAGENO=MIN; >3 %LET _CLIENTTASKLABEL=%NRBQUOTE(rx_biplr_v3_2); >4 %LET _EGTASKLABEL=%NRBQUOTE(rx_biplr_v3_2); >5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(); >6 %LET _SASPROGRAMFILE=; >7 >8 ODS _ALL_ CLOSE; >NOTE: Some of your options or statements may not be supported with the >Activex or Java series of devices. Graph > defaults for these drivers may be different from other SAS/GRAPH >device drivers. For further information, please > contact Technical Support. >9 OPTIONS DEV=ACTIVEX; >10 FILENAME EGHTML TEMP; >NOTE: Writing HTML(EGHTML) Body file: EGHTML >11 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault >11 ! STYLESHEET=(URL="file:///C:/Program%20Files/SAS/Shared% >20Files/BIClientStyles/EGDefault.css") >11 ! ATTRIBUTES= >("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") >NOGTITLE NOGFOOTNOTE >11 ! GPATH=&sasworklocation; >12 >13 %gaccessible; >14 /********************************************************/ >15 * RX_EXTRACT_V3.SAS ; >16 /********************************************************/ >17 >18 dm 'log;clear;out;clear'; >19 options linesize =120; >20 Option obs=0 NoReplace; >21 >22 >************************************************************************** * >*************; >23 *Date Programmer Reviewed By WHAT WAS DONE; >24 >25 >************************************************************************** * >*************; >26 >27 %let begdate='01SEP2009'd; >28 %let enddate='30SEP2009'd; >29 %let q = %str(%') ; >30 >31 > > >32 data _null_ ; >33 call symput('fdos',put(intnx('month',"&begdate"d,- >27,'beginning'), date9.) ) ; >34 call symput('ldos',put(intnx('month',"&enddate"d,- >0,'ending'), date9.) ) ; >35 run ; > >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.01 seconds > > >36 >37 >38 >39 **************************************************************; >40 * ??Use the data _null_ step to create a macro variable for >the ; >41 * year and month based on the ldos macro variable in the data ; >42 * step above?? ; >43 **************************************************************; >44 >45 >46 data _null_ ; >47 if month("&ldos"d) < 10 >48 then call symput('yr_mo',compress(year("&ldos"d) || '0' || >month("&ldos"d) ) ) ; >49 else call symput('yr_mo',compress(year("&ldos"d) || month >("&ldos"d) ) ) ; >50 run ; > >NOTE: Numeric values have been converted to character values at the places >given by: (Line):(Column). > 48:39 48:64 49:39 49:57 >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.00 seconds > > >51 >52 *%put &biplr_dx; >53 %put &begdate ; >'01SEP2009'd >54 %put &enddate ; >'30SEP2009'd >55 %put &fdos ; >01JUN2007 >56 %put &ldos ; >30SEP2009 >57 %put &q ; >' >58 >59 >60 libname biplr'/home/jshafi01/projects/adhoc/biplr_dprsn/data'; >NOTE: Libref BIPLR was successfully assigned as follows: > Engine: V9 > Physical Name: /home/jshafi01/projects/adhoc/biplr_dprsn/data >61 libname rosdwp oracle user="&oracle_user." pass="&oracle_pass." >buffsize=32767 path='rosdwp'; >NOTE: Libref ROSDWP was successfully assigned as follows: > Engine: ORACLE > Physical Name: rosdwp >62 >63 data work.biplr_up_v1; >64 attrib mbr_id length=$26; >65 set biplr.biplr_ubh_med_v1; >66 run; > >NOTE: There were 0 observations read from the data set >BIPLR.BIPLR_UBH_MED_V1. >NOTE: The data set WORK.BIPLR_UP_V1 has 0 observations and 1 variables. >NOTE: DATA statement used (Total process time): > real time 0.00 seconds > cpu time 0.01 seconds > > >67 >68 proc contents; >69 run; > >NOTE: PROCEDURE CONTENTS used (Total process time): > real time 0.02 seconds > cpu time 0.02 seconds > >70 >71 proc datasets >72 library = rosdwp nolist; >73 delete biplr_up_v1; >74 quit; > >NOTE: Deleting ROSDWP.BIPLR_UP_V1 (memtype=DATA). >NOTE: PROCEDURE DATASETS used (Total process time): > real time 0.67 seconds > cpu time 0.00 seconds > > >75 >76 proc datasets >77 library=work nolist; >78 copy out = rosdwp move; >79 select biplr_up_v1 ; >80 quit; > >NOTE: Moving WORK.BIPLR_UP_V1 to ROSDWP.BIPLR_UP_V1 (memtype=DATA). >NOTE: SAS variable labels, formats, and lengths are not written to DBMS >tables. >NOTE: There were 0 observations read from the data set WORK.BIPLR_UP_V1. >NOTE: The data set ROSDWP.BIPLR_UP_V1 has 0 observations and 1 variables. >NOTE: PROCEDURE DATASETS used (Total process time): > real time 0.12 seconds > cpu time 0.02 seconds > > >81 >82 >83 >84 >************************************************************************** * >**; >85 >************************************************************************** * >*; >86 >87 >88 proc SQL noprint ; >89 connect to oracle as rosdwp (user="&oracle_user." >password="&oracle_pass" buffsize=32767 path="rosdwp" >89 ! preserve_comments); >90 execute(alter session set nls_date_format = 'ddmonyyyy') >by rosdwp; >91 CREATE table biplr_rx as >92 SELECT >93 mrb_id, >94 pcp_spc, >95 rx_date, >96 brand, >97 generic, >98 ahfs_cd, >99 ahfs_dsc, >100 lbl, >101 strngth, >102 unt, >103 spc_tx_cls_cd, >104 spc_tx_cls_dsc, >105 gcn, >106 rx_num, >107 rx_ingrd, >108 rx_paid, >109 rx_cnt, >110 rx_qty, >111 rx_day >112 FROM connection to rosdwp >113 (SELECT >114 mid.UNIQ_MBR_ID as mrb_id, >115 pd.PRVDR_SPCLTY_DESC as pcp_spc, >116 pcf.LAST_SRVC_DT as rx_date, >117 dd.BRAND_NAME as brand, >118 dd.GNRC_NAME as generic, >119 dd.AHFS_THRPTC_CLS_CD as ahfs_cd, >120 dd.AHFS_THRPTC_CLS_DESC as ahfs_dsc, >121 dd.LABEL_NAME as lbl, >122 dd.STRNGTH_NUM as strngth, >123 dd.STRNGTH_UNIT_DESC as unt, >124 dd.SPECF_THRPTC_CLS_CD as spc_tx_cls_cd, >125 dd.SPECF_THRPTC_CLS_DESC as spc_tx_cls_dsc, >126 dd.GCN_NUM as gcn, >127 pcf.PRSCRPTN_NUM as rx_num, >128 SUM(pcf.INGRDNT_AMT) as rx_ingrd, >129 SUM(pcf.PD_AMT) as rx_paid, >130 SUM(pcf.UNIT_SRVC_CNT) as rx_cnt, >131 SUM(pcf.DSPNSD_QTY) as rx_qty, >132 SUM(pcf.DAY_SUPLY_CNT) as rx_day >133 FROM >134 PHRMCY_CLM_FACT pcf, >135 MBR_ID_DMNSN mid, >136 DRUG_DMNSN dd, >137 PRVDR_DMNSN pd >138 WHERE >139 (biplr_up_v1.mbr_id = MBR_ID_DMNSN.UNIQ_MBR_ID) And >140 (PHRMCY_CLM_FACT.FINL_CLM_KEY = >PHRMCY_FINL_CLM_DMNSN.FINL_CLM_KEY) AND >141 (PHRMCY_CLM_FACT.MBR_KEY = MBR_ID_DMNSN.MBR_KEY) And >142 (PHRMCY_CLM_FACT.BNFT_KEY = MBR_BNFT_DMNSN.BNFT_KEY) And >143 (PHRMCY_CLM_FACT.DRUG_KEY = DRUG_DMNSN.DRUG_KEY) And >144 (PHRMCY_FINL_CLM_DMNSN.CLM_ORGN_CD IN ('M','A')) AND >145 (PHRMCY_CLM_FACT.LAST_SRVC_DT >BETWEEN >&q.&fdos.&q and &q.&ldos.&q) >146 GROUP BY >147 mid.UNIQ_MBR_ID , >148 pd.PRVDR_SPCLTY_DESC , >149 pcf.LAST_SRVC_DT , >150 dd.BRAND_NAME , >151 dd.GNRC_NAME , >152 dd.AHFS_THRPTC_CLS_CD , >153 dd.AHFS_THRPTC_CLS_DESC , >154 dd.LABEL_NAME , >155 dd.STRNGTH_NUM , >156 dd.STRNGTH_UNIT_DESC , >157 dd.SPECF_THRPTC_CLS_CD , >158 dd.SPECF_THRPTC_CLS_DESC , >159 dd.GCN_NUM , >160 pcf.PRSCRPTN_NUM >161 ); >ERROR: ORACLE prepare error: ORA-00936: missing expression. SQL statement: >SELECT mid.UNIQ_MBR_ID as mrb_id, > pd.PRVDR_SPCLTY_DESC as pcp_spc, pcf.LAST_SRVC_DT as rx_date, >dd.BRAND_NAME as brand, dd.GNRC_NAME as generic, > dd.AHFS_THRPTC_CLS_CD as ahfs_cd, dd.AHFS_THRPTC_CLS_DESC as >ahfs_dsc, dd.LABEL_NAME as lbl, dd.STRNGTH_NUM as > strngth, dd.STRNGTH_UNIT_DESC as unt, dd.SPECF_THRPTC_CLS_CD as >spc_tx_cls_cd, dd.SPECF_THRPTC_CLS_DESC as > spc_tx_cls_dsc, dd.GCN_NUM as gcn, pcf.PRSCRPTN_NUM as rx_num, SUM >(pcf.INGRDNT_AMT) as rx_ingrd, SUM(pcf.PD_AMT) > as rx_paid, SUM(pcf.UNIT_SRVC_CNT) as rx_cnt, SUM(pcf.DSPNSD_QTY) >as rx_qty, SUM(pcf.DAY_SUPLY_CNT) as rx_day > FROM PHRMCY_CLM_FACT pcf, MBR_ID_DMNSN mid, DRUG_DMNSN dd, >PRVDR_DMNSN pd WHERE (biplr_up_v1.mbr_id = > MBR_ID_DMNSN.UNIQ_MBR_ID) And (PHRMCY_CLM_FACT.FINL_CLM_KEY = >PHRMCY_FINL_CLM_DMNSN.FINL_CLM_KEY) AND > (PHRMCY_CLM_FACT.MBR_KEY = MBR_ID_DMNSN.MBR_KEY) And >(PHRMCY_CLM_FACT.BNFT_KEY = MBR_BNFT_DMNSN.BNFT_KEY) And > (PHRMCY_CLM_FACT.DRUG_KEY = DRUG_DMNSN.DRUG_KEY) And >(PHRMCY_FINL_CLM_DMNSN.CLM_ORGN_CD IN ('M','A')) AND > (PHRMCY_CLM_FACT.LAST_SRVC_DT BETWEEN between '01JUN2007' >and '30SEP2009') GROUP BY mid.UNIQ_MBR_ID , > pd.PRVDR_SPCLTY_DESC , pcf.LAST_SRVC_DT , dd.BRAND_NAME , >dd.GNRC_NAME , dd.AHFS_THRPTC_CLS_CD , > dd.AHFS_THRPTC_CLS_DESC , dd.LABEL_NAME , dd.STRNGTH_NUM , >dd.STRNGTH_UNIT_DESC , dd.SPECF_THRPTC_CLS_CD , > dd.SPECF_THRPTC_CLS_DESC , dd.GCN_NUM , pcf.PRSCRPTN_NUM. >NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of >statements. >162 execute (drop table biplr_up_v1) by rosdwp; >NOTE: Statement not executed due to NOEXEC option. >163 disconnect from rosdwp; >NOTE: Statement not executed due to NOEXEC option. >164 quit; >NOTE: The SAS System stopped processing this step because of errors. >NOTE: PROCEDURE SQL used (Total process time): > real time 0.12 seconds > cpu time 0.00 seconds > >165 >166 >167 >168 >169 %LET _CLIENTTASKLABEL=; >170 %LET _EGTASKLABEL=; >171 %LET _CLIENTPROJECTNAME=; >172 %LET _SASPROGRAMFILE=; >173 >174 ;*';*";*/;quit;run; >175 ODS _ALL_ CLOSE; >176 >177 >178 QUIT; RUN; >179
From: J Shafiroff on 30 Oct 2009 11:41 Hi Daniela and Gerhard, Thank you for your suggestions. I will try the suggested uppercase suggestion. As to the enclosing brace (line nnn) that (was intended) to go together with "SELECT ( " at line 161. Best Regards, Jeff
From: J Shafiroff on 30 Oct 2009 11:42 My error: should read ")" at 161 should go with "SELECT (" at line 113. Thank You
From: Gerhard Hellriegel on 30 Oct 2009 11:54
right, I see it. Was too long and I've forgotten the ( when I saw the )... Another idea: because the statements are executed in ORACLE - could you test it in ORACLE without SAS to verify the syntax? Gerhard On Fri, 30 Oct 2009 11:42:54 -0400, J Shafiroff <jeff.shafiroff(a)BLUESHIELDCA.COM> wrote: >My error: should read ")" at 161 should go with "SELECT (" at line 113. > >Thank You |