topic Re: SAS EG 4.1 Code Help in SAS Enterprise Guide

SAS EG 4.1 Code Help

D_Z_ — Tue, 28 Feb 2012 19:08:53 GMT

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;

Re: SAS EG 4.1 Code Help

Hima — Tue, 28 Feb 2012 19:56:51 GMT

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;

Re: SAS EG 4.1 Code Help

D_Z_ — Tue, 28 Feb 2012 20:15:52 GMT

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.

Re: SAS EG 4.1 Code Help

Hima — Tue, 28 Feb 2012 20:20:08 GMT

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.

SAS EG 4.1 Code Help

D_Z_ — Tue, 28 Feb 2012 20:40:29 GMT

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;

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 ! ;

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

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

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

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.

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

78 %LET _CLIENTTASKLABEL=;

79 %LET _EGTASKLABEL=;

80 %LET _CLIENTPROJECTNAME=;

81 %LET _SASPROGRAMFILE=;

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

84 ODS _ALL_ CLOSE;

87 QUIT; RUN;

SAS EG 4.1 Code Help

Hima — Tue, 28 Feb 2012 20:47:48 GMT

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

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;

SAS EG 4.1 Code Help

D_Z_ — Tue, 28 Feb 2012 23:56:17 GMT

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!