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