Date: Fri, 30 Oct 2009 05:13:28 -0400
Reply-To: Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Gerhard Hellriegel <gerhard.hellriegel@T-ONLINE.DE>
Subject: 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@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
| 
