Hi All, I am bashing my head against a wall and need some help! I have written this query and its throwing an error and I cannot find the error. I have bee looking for about a day to find the error and have still not been able to find it. Can someone help me understand where my error may be. I am attaching the code and underneath will attach my log. I have double checked the references and they are all accurate. It's got to be missing a parathesis somewhere or a comma... but i cannot get it for the life of me. Any help is appreciated. /code data _null_; call symput("Start_Date",put(&Start_Date,date9.)); call symput("End_Date",put(&End_Date,date9.)); call symput("Del_Start_Date",put(&Del_Date_Start,date9.)); call symput("Del_End_Date",put(&Del_Date_End,date9.)); run; %put Start_Date Equal To: &Start_Date; %put End_Date Equal To: &End_Date; %put Del_Start_Date Equal To: &Del_Date_Start; %put Del_End_Date Equal To: &Del_Date_End; Proc SQL noprint; connect to oracle (user='XXXXXX' orapw='XXXXXXX' path=XXXXX preserve_comments); option missing = ''; create table work.temp_Orders as select * from connection to oracle ( SELECT distinct AO.DT_CRTD AS ORDER_PLACED, AO.SHIPPING_DATE, AO.AGREED_DELIVERY_DATETIME AS DELIVERY_DATE, AO.CPY_ABBR, AO.ODR_NUMBER, AO.RECIPIENT_FIRST_NAME AS FIRST_NAME, AO.RECIPIENT_LAST_NAME AS LAST_NAME, AD.STREET_ADDRESS_1, AD.STREET_ADDRESS_2, CI.NAME, AD.STE_CODE, AD.ZIP_ZIP_CODE, CASE (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as MERCH_AMOUNT, AO.SERVICE_CHARGE_AMOUNT, AO.TAX_AMOUNT, AO.DISCOUNT_AMOUNT, AO.TOTAL_AMOUNT, AI.MASTER_PRODUCT, OA.FIT_ID AS VENDOR_ID, AO.TRACKING_NUMBER, AF.NAME FROM ATS_ORDERS AO, XXX_ADDRESSES AD, XXX_ORDER_ITEMS AI, XXX_PRODUCTS AP, XXX_ORDER_ASSIGNS OA, XXX_FLORISTS AF, XXX_TELEPHONES AT, XXX_CITIES CI WHERE AO.DT_CRTD >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))-30 AND AO.DT_CRTD < trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1) AND AO.AGREED_DELIVERY_DATETIME >= trunc(to_date(%str(%'&Del_Date_Start%'),'ddmonyyyy')) AND AO.AGREED_DELIVERY_DATETIME < trunc(to_date(%str(%'&Del_Date_End%'),'ddmonyyyy')+1) and AO.CPY_ABBR = (%str(%'&Comp_Code%')) AND AO.ODR_NUMBER = AT.ODR_ODR_NUMBER AND AO.ODR_NUMBER = AD.ODR_ODR_NUMBER AND AO.ODR_NUMBER = AI.ODR_ODR_NUMBER AND AO.ODR_NUMBER = OA.ODR_ODR_NUMBER AND AD.CITY_ID = CI.ID :smileyplus: AND OA.FIT_ID = AF.FIT_ID :smileyplus: AND AI.MASTER_PRODUCT = AP.PRODUCT_CODE :smileyplus: AND AO.Express_Product_Indicator = 'Y' ); Disconnect from Oracle; Quit; code/ /log 1 ;*';*";*/;quit;run; 2 OPTIONS PAGENO=MIN; 3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code); 4 %LET _EGTASKLABEL=%NRBQUOTE(Code); 5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(C:\Users\Work\Google Drive\SAS Projects\Orders By Company Code And Date.egp); 6 %LET _SASPROGRAMFILE=; 7 %LET Start_Date = '19Dec2012'd; 8 %LET End_Date = '19Dec2012'd; 9 %LET Del_Date_End = '18Dec2012'd; 10 %LET Comp_Code = %NRSTR(); 11 %LET Del_Date_Start = '19Dec2012'd; 12 13 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. 14 OPTIONS DEV=ACTIVEX; 15 FILENAME EGHTML TEMP; NOTE: Writing HTML(EGHTML) Body file: EGHTML 16 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault 16 ! STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS/Shared%20Files/BIClientStyles/EGDefault.css") 16 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation 16 ! ; 17 18 %gaccessible; WARNING: The Base Product product with which DATASTEP (2) is associated will be expiring soon, and is currently in warning mode to indicate this upcoming expiration. Most typically this warning period runs for 45 days. Please run PROC SETINIT to obtain more information on your warning period. 19 data _null_; 20 call symput("Start_Date",put(&Start_Date,date9.)); 21 call symput("End_Date",put(&End_Date,date9.)); 22 call symput("Del_Start_Date",put(&Del_Date_Start,date9.)); 23 call symput("Del_End_Date",put(&Del_Date_End,date9.)); 24 run; NOTE: DATA statement used (Total process time): real time 0.00 seconds cpu time 0.00 seconds 25 26 %put Start_Date Equal To: &Start_Date; Start_Date Equal To: 19DEC2012 27 %put End_Date Equal To: &End_Date; End_Date Equal To: 19DEC2012 28 %put Del_Start_Date Equal To: &Del_Date_Start; Del_Start_Date Equal To: '19Dec2012'd 29 %put Del_End_Date Equal To: &Del_Date_End; Del_End_Date Equal To: '18Dec2012'd WARNING: The Base Product product with which SQL (2) is associated will be expiring soon, and is currently in warning mode to indicate this upcoming expiration. Most typically this warning period runs for 45 days. Please run PROC SETINIT to obtain more information on your warning period. 30 Proc SQL noprint; 31 connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments); 32 option missing = ''; 33 create table work.temp_Orders as select * from connection to oracle 2 The SAS System 11:04 Wednesday, December 19, 2012 34 ( SELECT 35 distinct 36 AO.DT_CRTD AS ORDER_PLACED, 37 AO.SHIPPING_DATE, 38 AO.AGREED_DELIVERY_DATETIME AS DELIVERY_DATE, 39 AO.CPY_ABBR, 40 AO.ODR_NUMBER, 41 AO.RECIPIENT_FIRST_NAME AS FIRST_NAME, 42 AO.RECIPIENT_LAST_NAME AS LAST_NAME, 43 AD.STREET_ADDRESS_1, 44 AD.STREET_ADDRESS_2, 45 CI.NAME, 46 AD.STE_CODE, 47 AD.ZIP_ZIP_CODE, 48 CASE 49 (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as 49 ! MERCH_AMOUNT, 50 AO.SERVICE_CHARGE_AMOUNT, 51 AO.TAX_AMOUNT, 52 AO.DISCOUNT_AMOUNT, 53 AO.TOTAL_AMOUNT, 54 AI.MASTER_PRODUCT, 55 OA.FIT_ID AS VENDOR_ID, 56 AO.TRACKING_NUMBER, 57 AF.NAME 58 59 60 61 FROM xxx_ORDERS AO, 62 xxx_ADDRESSES AD, 63 xxx_ORDER_ITEMS AI, 64 xxx_PRODUCTS AP, 65 xxx_ORDER_ASSIGNS OA, 66 xxx_FLORISTS AF, 67 xxx_TELEPHONES AT, 68 xxx_CITIES CI 69 70 WHERE AO.DT_CRTD >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))-30 71 AND AO.DT_CRTD < trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1) 72 AND AO.AGREED_DELIVERY_DATETIME >= trunc(to_date(%str(%'&Del_Date_Start%'),'ddmonyyyy')) 73 AND AO.AGREED_DELIVERY_DATETIME < trunc(to_date(%str(%'&Del_Date_End%'),'ddmonyyyy')+1) 74 and AO.CPY_ABBR = (%str(%'&Comp_Code%')) 75 AND AO.ODR_NUMBER = AT.ODR_ODR_NUMBER 76 AND AO.ODR_NUMBER = AD.ODR_ODR_NUMBER 77 AND AO.ODR_NUMBER = AI.ODR_ODR_NUMBER 78 AND AO.ODR_NUMBER = OA.ODR_ODR_NUMBER 79 AND AD.CITY_ID = CI.ID :smileyplus: 80 AND OA.FIT_ID = AF.FIT_ID :smileyplus: 81 AND AI.MASTER_PRODUCT = AP.PRODUCT_CODE :smileyplus: 82 AND AO.Express_Product_Indicator = 'Y' 83 84 85 86 ); ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL statement: SELECT distinct AO.DT_CRTD AS ORDER_PLACED, AO.SHIPPING_DATE, AO.AGREED_DELIVERY_DATETIME AS DELIVERY_DATE, AO.CPY_ABBR, AO.ODR_NUMBER, AO.RECIPIENT_FIRST_NAME AS FIRST_NAME, AO.RECIPIENT_LAST_NAME AS LAST_NAME, AD.STREET_ADDRESS_1, AD.STREET_ADDRESS_2, CI.NAME, AD.STE_CODE, AD.ZIP_ZIP_CODE, CASE (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + 3 The SAS System 11:04 Wednesday, December 19, 2012 AO.manual_gift_cert_amount) as MERCH_AMOUNT, AO.SERVICE_CHARGE_AMOUNT, AO.TAX_AMOUNT, AO.DISCOUNT_AMOUNT, AO.TOTAL_AMOUNT, AI.MASTER_PRODUCT, OA.FIT_ID AS VENDOR_ID, AO.TRACKING_NUMBER, AF.NAME FROM ATS_ORDERS AO, ATS_ADDRESSES AD, ATS_ORDER_ITEMS AI, ATS_PRODUCTS AP, ATS_ORDER_ASSIGNS OA, ATS_FLORISTS AF, ATS_TELEPHONES AT, ATS_CITIES CI WHERE AO.DT_CRTD >= trunc(to_date('19DEC2012','ddmonyyyy'))-30 AND AO.DT_CRTD < trunc(to_date('19DEC2012','ddmonyyyy')+1) AND AO.AGREED_DELIVERY_DATETIME >= trunc(to_date(''19Dec2012'd','ddmonyyyy')) AND AO.AGREED_DELIVERY_DATETIME < trunc(to_date(''18Dec2012'd','ddmonyyyy')+1) and AO.CPY_ABBR = ('') AND AO.ODR_NUMBER = AT.ODR_ODR_NUMBER AND AO.ODR_NUMBER = AD.ODR_ODR_NUMBER AND AO.ODR_NUMBER = AI.ODR_ODR_NUMBER AND AO.ODR_NUMBER = OA.ODR_ODR_NUMBER AND AD.CITY_ID = CI.ID :smileyplus: AND OA.FIT_ID = AF.FIT_ID :smileyplus: AND AI.MASTER_PRODUCT = AP.PRODUCT_CODE :smileyplus: AND AO.Express_Product_Indicator = 'Y'. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. 87 Disconnect from Oracle; NOTE: Statement not executed due to NOEXEC option. 88 Quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time 0.00 seconds cpu time 0.02 seconds 89 90 91 %LET _CLIENTTASKLABEL=; 92 %LET _EGTASKLABEL=; 93 %LET _CLIENTPROJECTNAME=; 94 %LET _SASPROGRAMFILE=; 95 96 ;*';*";*/;quit;run; 97 ODS _ALL_ CLOSE; 98 99 100 QUIT; RUN; 101 log /
... View more
