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