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 /
I can see 2 things where I would investigate a bit:
First:
Your date macro variable resolve to a SAS expression. Eg:
28 %put Del_Start_Date Equal To: &Del_Date_Start;
Del_Start_Date Equal To: '19Dec2012'd
But you're then using it in an Oracle expression: trunc(to_date(%str(%'&Del_Date_Start%'),'ddmonyyyy'))
You can see the following in the log: trunc(to_date(''19Dec2012'd','ddmonyyyy'))
Besides of this not being valid Oracle syntax I also assume that this doubled single quotation marks mess up things a lot - so the error you're getting is kind of "random".
Second:
I don't understand your CASE statement. Is this a valid syntax?
CASE(AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as MERCH_AMOUNT,
Shouldn't this be something like CASE WHEN ... THEN... ELSE... END
Try moving your statement: option missing = ''; before your PROC SQL statement. This is an "open code" statement that cannot be used inside a PROC or DATA step. Also OPTIONS is the correct word to use, although OPTION will still work.
I removed that entire statement... cause it was optional for my query... got the same error message.
I can see 2 things where I would investigate a bit:
First:
Your date macro variable resolve to a SAS expression. Eg:
28 %put Del_Start_Date Equal To: &Del_Date_Start;
Del_Start_Date Equal To: '19Dec2012'd
But you're then using it in an Oracle expression: trunc(to_date(%str(%'&Del_Date_Start%'),'ddmonyyyy'))
You can see the following in the log: trunc(to_date(''19Dec2012'd','ddmonyyyy'))
Besides of this not being valid Oracle syntax I also assume that this doubled single quotation marks mess up things a lot - so the error you're getting is kind of "random".
Second:
I don't understand your CASE statement. Is this a valid syntax?
CASE(AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as MERCH_AMOUNT,
Shouldn't this be something like CASE WHEN ... THEN... ELSE... END
Thanks Patrick! I had removed my case statement and didn't notice i had the CASE still in the query. Appreciate your eyes on that one.
As far as the other part... it works fine for me. I have never had an issue using the oracle expression in that way. I use it on almost all of my reports that i need to use dates. I am not sure why it spit out two quotes... when i ran it again... it didn't do that.
Dean
@Dean
"I am not sure why it spit out two quotes"
That's because in the pass-through SQL you've used macro variables &Del_Date_Start and &Del_Date_End instead of &Del_Start_Date and &Del_End_Date. You might consider to change your naming conventions so that you can spot such typos faster.
And yes, of course, if you're using the correct macro variables then the Oracle syntax is valid.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.