BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
D_Z_
Obsidian | Level 7

Hey Everyone!

I am a little above my head with this one.  I finally got my code to work to pull the data that i needed.  The only thing i have left to do with this is to figure out how to get the two last custom fields to fill in.  Here is what i have so far below.  What I am trying to figure out how to do is create two fields... i am looking at Merch_Amount and comparing it to MS.CHANGED_PRICE..

1st field - if changed_price => Merch_Amount... then put it in the column... if not then leave it blank

2nd field - if changed_price < Merch Amount, then put it in the column, otherwise leave it blank

I am not sure how to write the last bit of code for this one.  Can anyone help?

data _null_;

      call symput("Start_Date",put(&START_DATE,date9.));

      call symput("End_Date",put(&END_DATE,date9.));

run;

%put Start Date Equal To: &START_DATE;

%put End Date Equal To: &END_DATE;

Proc SQL noprint;

connect to oracle (user='XXXX' orapw='XXXX'path=XXXX preserve_comments);

create tablework.TEMP_XX01 as select * from connection to oracle

  ( SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO)  */

        distinct AO.ODR_NUMBER,

        AMQ.COMPLETED_DATE,

        AQ.ID,

      AM.XXX_shop,

        (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.total_amount,

      MS.PRICE_CHANGE_CODE,

      MS.CHANGED_PRICE,

      AMQ.usr_mdfd

     FROM  X_QUEUES               AQ,

           X_MESSAGE_QUEUES       AMQ,

           X_MESSAGES             AM,

           X_ORDERS               AO ,

           X_CSIS                 CS,

           X_CSI_QUEUES           CQ ,

           X_MESSAGES             MS

     WHERE AMQ.COMPLETED_DATE >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))

     AND   AMQ.COMPLETED_DATE <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)

     AND   AO.ODR_NUMBER = AM.ODR_ODR_NUMBER      

     AND   AO.VERSION    = AM.ODR_VERSION         

     AND   AM.ID         = AMQ.MSG_ID             

       AND   AMQ.QQQ_ID    = AQ.ID                  

       AND   AQ.QUEUE_TYPE = 'DEN'                  

       AND   AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+)   

     AND   AO.VERSION    = CS.ODR_VERSION(+)      

     AND   CS.ID         = CQ.CSI_ID  :smileyplus:            

     AND   AO.ODR_NUMBER = MS.ODR_ODR_NUMBER  

     AND   MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER 

     AND   AO.VERSION    = MS.ODR_VERSION         

     AND   MS.MSG_TYPE IN ('ASK','CAN','ANS')   /* For the price change infotmation - the price change code is 'P'

                                                    and the message_type in ('ASK','ANS') and also the direction = 'O'.

                                                     Not sure why you need 'CAN' in the filter*/       

     AND   MS.DIRECTION = 'O'

     AND   MS.ID > AM.ID

     AND   MS.FIT_ID = AM.FIT_Id

   );

Disconnect from Oracle;

Quit;

1 ACCEPTED SOLUTION

Accepted Solutions
Hima
Obsidian | Level 7

Sorry it was actually this. You coded incompletely the case statement. it has to be end as column_name

39 case

40 when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

2 The SAS System 14:51 Tuesday, February 28, 2012

40 ! AO.manual_gift_cert_amount) then changed_price AS Full_Payment

41 when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

41 ! AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

41 ! AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment

42 else ' ' end

Correct code:

case  when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then changed_price AS Full_Payment

         when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment

else ' ' end  as column_name 

from

FROM  X_QUEUES               AQ,

      X_MESSAGE_QUEUES       AMQ,

      X_MESSAGES             AM,

      X_ORDERS               AO ,

      X_CSIS                 CS,

      X_CSI_QUEUES           CQ ,

      X_MESSAGES             MS

WHERE AMQ.COMPLETED_DATE >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))

      AND   AMQ.COMPLETED_DATE <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)

      AND   AO.ODR_NUMBER = AM.ODR_ODR_NUMBER      

      AND   AO.VERSION    = AM.ODR_VERSION         

      AND   AM.ID         = AMQ.MSG_ID             

      AND   AMQ.QQQ_ID    = AQ.ID                  

      AND   AQ.QUEUE_TYPE = 'DEN'                  

      AND   AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+)   

      AND   AO.VERSION    = CS.ODR_VERSION(+)      

      AND   CS.ID         = CQ.CSI_ID              

      AND   AO.ODR_NUMBER = MS.ODR_ODR_NUMBER  

      AND   MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER 

      AND   AO.VERSION    = MS.ODR_VERSION         

      AND   MS.MSG_TYPE IN ('ASK','CAN','ANS')  

      AND   MS.DIRECTION = 'O'

      AND   MS.ID > AM.ID

      AND   MS.FIT_ID = AM.FIT_Id

);

Disconnect from Oracle;

Quit;

View solution in original post

6 REPLIES 6
Hima
Obsidian | Level 7

The conditions are little bit unclear but I attempted to help you. Please adjust the column names to your needs. Using a case statement will serve the purpose. See the code in bold.

data _null_;
call symput("Start_Date",put(&START_DATE,date9.));
call symput("End_Date",put(&END_DATE,date9.));
run;

%put Start Date Equal To: &START_DATE;
%put End Date Equal To: &END_DATE;

Proc SQL noprint;
connect to oracle (user='SASAPP' orapw='SASAPP0RA' path=R800P preserve_comments);
create table work.TEMP_DZ01 as select * from connection to oracle
(SELECT distinct AO.ODR_NUMBER, AMQ.COMPLETED_DATE, AQ.ID, AM.XXX_shop,
(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.total_amount, MS.PRICE_CHANGE_CODE, MS.CHANGED_PRICE,
AMQ.usr_mdfd,
case

     when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then changed_price ** change the column name if this doesnot meet your conditiin **;
     when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then  (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount)  ** change the column name if this doesnot meet your conditiin **;

else ' ' end as col_name
FROM  X_QUEUES               AQ,
      X_MESSAGE_QUEUES       AMQ,
      X_MESSAGES             AM,
      X_ORDERS               AO ,
      X_CSIS                 CS,
      X_CSI_QUEUES           CQ ,
      X_MESSAGES             MS
WHERE AMQ.COMPLETED_DATE >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))
      AND   AMQ.COMPLETED_DATE <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)
      AND   AO.ODR_NUMBER = AM.ODR_ODR_NUMBER      
      AND   AO.VERSION    = AM.ODR_VERSION         
      AND   AM.ID         = AMQ.MSG_ID             
      AND   AMQ.QQQ_ID    = AQ.ID                  
      AND   AQ.QUEUE_TYPE = 'DEN'                  
      AND   AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+)   
      AND   AO.VERSION    = CS.ODR_VERSION(+)      
      AND   CS.ID         = CQ.CSI_ID              
      AND   AO.ODR_NUMBER = MS.ODR_ODR_NUMBER  
      AND   MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER 
      AND   AO.VERSION    = MS.ODR_VERSION         
      AND   MS.MSG_TYPE IN ('ASK','CAN','ANS')  
      AND   MS.DIRECTION = 'O'
      AND   MS.ID > AM.ID
      AND   MS.FIT_ID = AM.FIT_Id
);
Disconnect from Oracle;
Quit;

D_Z_
Obsidian | Level 7

I apologize for not being more clear.

Here is the case statement i was trying to accomplish

case

when changed_price >= MERCH_AMOUNT then changed_price AS Full_Payment

when changed_price < MERCH_AMOUNT then (MERCH_AMOUNT - Changed_Price) as Partial_Payment

else ' ' end

The fields do not currently exist in the database. so i I am trying to create the fields within the statement.  I hope that what i modified gives a little more insite on what i am trying to do here... the statement as it is above throws an error.

Hima
Obsidian | Level 7

Try running this and it should work. if you get an error please post the log so I can help you better.

case  when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then changed_price AS Full_Payment

         when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment

else ' ' end

Just replace  MERCH_AMOUNT with  (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) and it should work fine.

D_Z_
Obsidian | Level 7

Here is the Log for the error it produced...

1 ;*';*";*/;quit;run;

2 OPTIONS PAGENO=MIN;

3 %LET _CLIENTTASKLABEL=%NRBQUOTE(Code);

4 %LET _EGTASKLABEL=%NRBQUOTE(Code);

5 %LET _CLIENTPROJECTNAME=%NRBQUOTE(C:\Users\Work\Documents\SAS Projects\DENY_REPORT_CODE.egp);

6 %LET _SASPROGRAMFILE=;

7 %LET Start_Date = '27Feb2012'd;

8 %LET End_Date = '27Feb2012'd;

9

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

11 OPTIONS DEV=ACTIVEX;

12 FILENAME EGHTML TEMP;

NOTE: Writing HTML(EGHTML) Body file: EGHTML

13 ODS HTML(ID=EGHTML) FILE=EGHTML ENCODING='utf-8' STYLE=EGDefault

13 ! STYLESHEET=(URL="file:///C:/Program%20Files%20(x86)/SAS/Shared%20Files/BIClientStyles/EGDefault.css")

13 ! ATTRIBUTES=("CODEBASE"="http://www2.sas.com/codebase/graph/v91/sasgraph.exe") NOGTITLE NOGFOOTNOTE GPATH=&sasworklocation

13 ! ;

14

15 %gaccessible;

 

16 data _null_;

17 call symput("Start_Date",put(&START_DATE,date9.));

18 call symput("End_Date",put(&END_DATE,date9.));

19 run;

NOTE: DATA statement used (Total process time):

real time 0.00 seconds

cpu time 0.00 seconds

20

21 %put Start Date Equal To: &START_DATE;

Start Date Equal To: 27FEB2012

22 %put End Date Equal To: &END_DATE;

End Date Equal To: 27FEB2012

23

24 Proc SQL noprint;

25 connect to oracle (user='SASAPP' orapw=XXXXXXXXXXX path=R800P preserve_comments);

26 create table work.TEMP_DZ01 as select * from connection to oracle

27 ( SELECT/*+ INDEX (AO ODR_PK) USE_NL(AO) */

28 distinct AO.ODR_NUMBER,

29 AMQ.COMPLETED_DATE,

30 AQ.ID,

31 AM.bmt_shop,

32 (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) as

32 ! MERCH_AMOUNT,

33 AO.service_charge_amount,

34 AO.tax_amount,

35 AO.total_amount,

36 MS.PRICE_CHANGE_CODE,

37 MS.CHANGED_PRICE,

38 AMQ.usr_mdfd

39 case

40 when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

2 The SAS System 14:51 Tuesday, February 28, 2012

40 ! AO.manual_gift_cert_amount) then changed_price AS Full_Payment

41 when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

41 ! AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

41 ! AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment

42 else ' ' end

43

44

45 FROM ATS_QUEUES AQ,

46 ATS_MESSAGE_QUEUES AMQ,

47 ATS_MESSAGES AM,

48 ATS_ORDERS AO ,

49 ATS_CSIS CS,

50 ATS_CSI_QUEUES CQ ,

51 ATS_MESSAGES MS

52 WHERE AMQ.COMPLETED_DATE >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))

53 AND AMQ.COMPLETED_DATE < trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)

54 AND AO.ODR_NUMBER = AM.ODR_ODR_NUMBER

55 AND AO.VERSION = AM.ODR_VERSION

56 AND AM.ID = AMQ.MSG_ID

57 AND AMQ.QQQ_ID = AQ.ID

58 AND AQ.QUEUE_TYPE = 'DEN'

59 AND AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+)

60 AND AO.VERSION = CS.ODR_VERSION(+)

61 AND CS.ID = CQ.CSI_ID :smileyplus:

62 AND AO.ODR_NUMBER = MS.ODR_ODR_NUMBER

63 AND MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER

64 AND AO.VERSION = MS.ODR_VERSION

65 AND MS.MSG_TYPE IN ('ASK','CAN','ANS') /* For the price change infotmation - the price change code is 'P'

66 and the message_type in ('ASK','ANS') and also the direction = 'O'.

67 Not sure why you need 'CAN' in the filter*/

68 AND MS.DIRECTION = 'O'

69 AND MS.ID > AM.ID

70 AND MS.FIT_ID = AM.FIT_Id

71 );

ERROR: ORACLE prepare error: ORA-00923: FROM keyword not found where expected. SQL statement: SELECT/*+ INDEX (AO ODR_PK)

USE_NL(AO) */ distinct AO.ODR_NUMBER, AMQ.COMPLETED_DATE, AQ.ID, AM.bmt_shop, (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.total_amount, MS.PRICE_CHANGE_CODE, MS.CHANGED_PRICE, AMQ.usr_mdfd case when changed_price >=

(AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then

changed_price AS Full_Payment when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount +

AO.discount_amount + AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount +

AO.discount_amount + AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment else ' ' end FROM ATS_QUEUES AQ,

ATS_MESSAGE_QUEUES AMQ, ATS_MESSAGES AM, ATS_ORDERS AO , ATS_CSIS CS, ATS_CSI_QUEUES CQ , ATS_MESSAGES MS WHERE

AMQ.COMPLETED_DATE >= trunc(to_date('27FEB2012','ddmonyyyy')) AND AMQ.COMPLETED_DATE <

trunc(to_date('27FEB2012','ddmonyyyy')+1) AND AO.ODR_NUMBER = AM.ODR_ODR_NUMBER AND AO.VERSION = AM.ODR_VERSION AND AM.ID =

AMQ.MSG_ID AND AMQ.QQQ_ID = AQ.ID AND AQ.QUEUE_TYPE = 'DEN' AND AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+) AND AO.VERSION =

CS.ODR_VERSION(+) AND CS.ID = CQ.CSI_ID :smileyplus: AND AO.ODR_NUMBER = MS.ODR_ODR_NUMBER AND MS.MER_ORDER_NUMBER =

AM.MER_ORDER_NUMBER AND AO.VERSION = MS.ODR_VERSION AND MS.MSG_TYPE IN ('ASK','CAN','ANS') /* For the price change

infotmation - the price change code is 'P' and the message_type in ('ASK','ANS') and also the direction = 'O'. Not sure why

you need 'CAN' in the filter*/ AND MS.DIRECTION = 'O' AND MS.ID > AM.ID AND MS.FIT_ID = AM.FIT_Id.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.

72

73 Disconnect from Oracle;

NOTE: Statement not executed due to NOEXEC option.

74 Quit;

NOTE: The SAS System stopped processing this step because of errors.

NOTE: PROCEDURE SQL used (Total process time):

real time 0.00 seconds

3 The SAS System 14:51 Tuesday, February 28, 2012

cpu time 0.01 seconds

75

76

77

78 %LET _CLIENTTASKLABEL=;

79 %LET _EGTASKLABEL=;

80 %LET _CLIENTPROJECTNAME=;

81 %LET _SASPROGRAMFILE=;

82

83 ;*';*";*/;quit;run;

84 ODS _ALL_ CLOSE;

85

86

87 QUIT; RUN;

Hima
Obsidian | Level 7

Sorry it was actually this. You coded incompletely the case statement. it has to be end as column_name

39 case

40 when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

2 The SAS System 14:51 Tuesday, February 28, 2012

40 ! AO.manual_gift_cert_amount) then changed_price AS Full_Payment

41 when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

41 ! AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount +

41 ! AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment

42 else ' ' end

Correct code:

case  when changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then changed_price AS Full_Payment

         when changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then ((AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) - Changed_Price) as Partial_Payment

else ' ' end  as column_name 

from

FROM  X_QUEUES               AQ,

      X_MESSAGE_QUEUES       AMQ,

      X_MESSAGES             AM,

      X_ORDERS               AO ,

      X_CSIS                 CS,

      X_CSI_QUEUES           CQ ,

      X_MESSAGES             MS

WHERE AMQ.COMPLETED_DATE >= trunc(to_date(%str(%'&Start_Date%'),'ddmonyyyy'))

      AND   AMQ.COMPLETED_DATE <  trunc(to_date(%str(%'&End_Date%'),'ddmonyyyy')+1)

      AND   AO.ODR_NUMBER = AM.ODR_ODR_NUMBER      

      AND   AO.VERSION    = AM.ODR_VERSION         

      AND   AM.ID         = AMQ.MSG_ID             

      AND   AMQ.QQQ_ID    = AQ.ID                  

      AND   AQ.QUEUE_TYPE = 'DEN'                  

      AND   AO.ODR_NUMBER = CS.ODR_ODR_NUMBER(+)   

      AND   AO.VERSION    = CS.ODR_VERSION(+)      

      AND   CS.ID         = CQ.CSI_ID              

      AND   AO.ODR_NUMBER = MS.ODR_ODR_NUMBER  

      AND   MS.MER_ORDER_NUMBER = AM.MER_ORDER_NUMBER 

      AND   AO.VERSION    = MS.ODR_VERSION         

      AND   MS.MSG_TYPE IN ('ASK','CAN','ANS')  

      AND   MS.DIRECTION = 'O'

      AND   MS.ID > AM.ID

      AND   MS.FIT_ID = AM.FIT_Id

);

Disconnect from Oracle;

Quit;

D_Z_
Obsidian | Level 7

This is the final code that worked.  Thank you so much for your help with this.  This was an immense help to get this report out.

case

when MS.changed_price >= (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then MS.changed_price

      else 0 end as Full_Payment,

case

when MS.changed_price < (AO.total_amount - AO.tax_amount - AO.service_charge_amount + AO.discount_amount + AO.manual_gift_cert_amount) then MS.Changed_Price

        else 0 end as Partial_Payment

I needed two different fields for two different types of data, so i had to make two case statements and it works beautifully now.  I could not of done it without the starter that you gave me.  Thanks so much!

DZ

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 6 replies
  • 1043 views
  • 3 likes
  • 2 in conversation