Solved: Re: SAS EG 4.1 Coding Help

D_Z_ · Posted 12-19-2012 01:29 PM

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 /

Patrick · Posted 12-19-2012 03:13 PM

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

View solution in original post

SASKiwi · Posted 12-19-2012 02:25 PM

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.

D_Z_ · Posted 12-19-2012 02:38 PM

I removed that entire statement... cause it was optional for my query... got the same error message.

Patrick · Posted 12-19-2012 03:13 PM

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

D_Z_ · Posted 12-19-2012 10:17 PM

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

Patrick · Posted 12-20-2012 03:34 AM

@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.

Classroom Training Available!