Desktop productivity for business analysts and programmers

SAS EG 4.1 Coding Help

Accepted Solution Solved
Reply
Contributor
Posts: 70
Accepted Solution

SAS EG 4.1 Coding Help

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 /


Accepted Solutions
Solution
‎12-19-2012 03:13 PM
Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 Coding Help

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


All Replies
Super User
Posts: 3,233

Re: SAS EG 4.1 Coding Help

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.

Contributor
Posts: 70

Re: SAS EG 4.1 Coding Help

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

Solution
‎12-19-2012 03:13 PM
Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 Coding Help

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

Contributor
Posts: 70

Re: SAS EG 4.1 Coding Help

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

Respected Advisor
Posts: 4,132

Re: SAS EG 4.1 Coding Help

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

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 574 views
  • 0 likes
  • 3 in conversation