Thanks! Let me try this ... View more

Hi Supre_User,   Use Libname or Pass thru to connect, and it did work.   Thank you for you help.   ... View more

Hi Chris!   I will make a note of this!     Thank you for your help!     ... View more

Hi Chris!   Thank  you for you reply!   I am trying to do a lookup from oracle table and SAS Dataset to check the sales and area, to create a report of sales and salesperson for dates and on oracle tables the dates are -'01-JAN-00' . please suggest if i can use cast to convert the date only for comparison. ... View more

Thank  you Tom!     It worked.   I was missing the smallest detail.       ... View more

Hi All, Apologies!   Please find the Logs for Pass Thru and LIBNAME.   Please note that we use a macro to connect to Oracle or any database. LOG Pass Thru:   SUSER@LINUXPATH/dir1/path1/xxxx/abc/yymm2405/input/_result/oracle_load %LET (variable name is PREVMLOGIC) MLOGIC (GET_ORACLE_LOGIN2): MPRINT (GET_ORACLE_LOGIN2): options nomautolocdisplay nomprint nonotes ******** Macro GET_ORACLE_LOGIN2: unencrypts Oracle password and calls either Proc SQL or Libname ******** >> Optional: "ORACLE_CONNECTION_METHOD" defaults to PASSTHRU - otherwise must be either LIBNAME or (for Proc SQL) passthru >> Required: "DATABASE" can be 6 char or 10 (eg uatdb1) but macro converts 6-char DB names to 10-char DB service names and must be one of these: sales1_dev devdb1 devdb2 devdb3 devdb4 uatdb1 uatdb2 uatdb3 uatdb4 prddb1 prddb2 prddb3 prddb4 >> Required: "SCHEMA" requried only ==IF== LIBNAME is specified for oracle_connection_method then must be <= 8 char >> Optional: "USER" defaults to USER if blank otherwise must be user or user1 >> Optional: "PWDIR" defaults to /$HOSTNAME/usr/cmfprod but can be changed >> Optional: "OPTIONAL_CONNECT_PARM" defaults to readbuff-1000 for USER ID. env dev If you are using SAS/Oracle to update oracle, then add dbcommit=10000 ************************************************** *************************** ******************* >> env = dev LIBNAME SALESR ORACLE PATH=d01bri_dev SCHEMA-SALESR MPRINT (GET_DETAIL.GET_ORACLE_LOGIN2): USER=user1 ; SOURCE MPRINTNEST SYMBOLGEN MLOGIC; MLOGIC (GET ORACLE_LOGIN2): Ending execution. MPRINT (GET DETAIL): MPRINT (GET_DETAIL): proc sql; SYMBOLGEN: Macro variable ORA_DATABASE resolves to sales1_dev SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. ERROR: Librefs are restricted to eight characters. sales1_dev has been truncated. MPRINT (GET_DETAIL): select table_name, owner from sales1_dev.dba_tables WHERE OWNER in ('OWNER1','SALESR'); NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. MPRINT (GET_DETAIL): disconnect from oracle ; NOTE: Statement not executed due to NOEXEC option. MPRINT (GET_DETAIL): quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time cpu time MLOGIC (GET_DETAIL): 0.00 seconds 0.00 seconds PUT "SCHEMA ====== &ora_schema. SYMBOLGEN: Macro variable ORA_SCHEMA resolves to SALESR SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. "SCHEMA =➖➖➖➖➖ SALESR MLOGIC (GET_DETAIL): " Ending execution. 60 61 62 63 64   Logs - LIBNAME: SUSER@LINUXPATH/dir1/path1/xxxx/abc/yymm2405/input/_result/oracle_load %LET (variable name is PREVMLOGIC) MLOGIC (GET_ORACLE_LOGIN2): MPRINT (GET_ORACLE_LOGIN2): options nomautolocdisplay nomprint nonotes ******** Macro GET_ORACLE_LOGIN2: unencrypts Oracle password and calls either Proc SQL or Libname ******** >> Optional: "ORACLE_CONNECTION_METHOD" defaults to PASSTHRU - otherwise must be either LIBNAME or (for Proc SQL) passthru >> Required: "DATABASE" can be 6 char or 10 (eg uatdb1) but macro converts 6-char DB names to 10-char DB service names and must be one of these: sales1_dev devdb1 devdb2 devdb3 devdb4 uatdb1 uatdb2 uatdb3 uatdb4 prddb1 prddb2 prddb3 prddb4 >> Required: "SCHEMA" requried only ==IF== LIBNAME is specified for oracle_connection_method then must be <= 8 char >> Optional: "USER" defaults to USER if blank otherwise must be user or user1 >> Optional: "PWDIR" defaults to /$HOSTNAME/usr/cmfprod but can be changed >> Optional: "OPTIONAL_CONNECT_PARM" defaults to readbuff-1000 for USER ID. env dev If you are using SAS/Oracle to update oracle, then add dbcommit=10000 ************************************************** LIBNAME rcoe ORACLE PATH=sales1_dev SCHEMA=salesr USER=user1 password = xxxxxxxxxxxxxxxxx ; SOURCE MPRINTNEST SYMBOLGEN MLOGIC; Ending execution. MPRINT (GET DETAIL. GET ORACLE_LOGIN2): MLOGIC (GET ORACLE_LOGIN2): MPRINT (GET DETAIL): ; MPRINT (GET_DETAIL): libname temp ORACLE PATH=sales1_dev SCHEMA=salesr USER= user1 PASSWORD= xxxxxxxxxxxxxxxxxx; NOTE: Libref TEMP was successfully assigned as follows: Engine: Physical Name: ORACLE sales1_dev proc sql; MPRINT (GET_DETAIL): MPRINT (GET DETAIL): select table_name, owner from temp.dba_tables WHERE OWNER in ('OWNER1', 'SALESR'); ERROR: File TEMP.DBA_TABLES. DATA does not exist. NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements. MPRINT (GET_DETAIL): disconnect from oracle ; NOTE: Statement not executed due to NOEXEC option. MPRINT (GET DETAIL): quit; NOTE: The SAS System stopped processing this step because of errors. NOTE: PROCEDURE SQL used (Total process time): real time cpu time 0.00 seconds 0.01 seconds MLOGIC (GET_DETAIL): %PUT "SCHEMA ====== &ora_schema. SYMBOLGEN: Macro variable ORA SCHEMA resolves to SALESR SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing. "SCHEMA ====== SALESR MLOGIC (GET_DETAIL): 11 Ending execution. 62 63 64         ... View more

hi, I am too late for this   PROC SORT DATA = your_data; BY Customer_Number Transaction_Amount currency_key;RUN; data alerts; set your_data; format alert_date date9.; /* Check for transactions in the last 7 days with the same amount and currency */ by Customer_Number Transaction_Amount currency_key; retain count; if first.Customer_Number then count = 0; if date_key >= intnx('day', today(), -10) and lag(Transaction_Amount) = Transaction_Amount and lag(currency_key) = currency_key then do; count + 1; if count >= 4 then do; alert_date = today(); output; end; end; else count = 0; drop count; run;  but one can try   PROC SORT DATA = your_data; BY Customer_Number Transaction_Amount currency_key;RUN; data alerts; set your_data; format alert_date date9.; /* Check for transactions in the last 7 days with the same amount and currency */ by Customer_Number Transaction_Amount currency_key; retain count; if first.Customer_Number then count = 0; if date_key >= intnx('day', today(), -10) and lag(Transaction_Amount) = Transaction_Amount and lag(currency_key) = currency_key then do; count + 1; if count >= 4 then do; alert_date = today(); output; end; end; else count = 0; drop count; run; PROC SORT DATA = your_data; BY Customer_Number Transaction_Amount currency_key;RUN; data alerts; set your_data; format alert_date date9.; /* Check for transactions in the last 7 days with the same amount and currency */ by Customer_Number Transaction_Amount currency_key; retain count; if first.Customer_Number then count = 0; if date_key >= intnx('day', today(), -10) and lag(Transaction_Amount) = Transaction_Amount and lag(currency_key) = currency_key then do; count + 1; if count >= 4 then do; alert_date = today(); output; end; end; else count = 0; drop count; run;   ... View more

Thank you All!     ... View more

Hi,   Thank you for your quick response! I will try this.       ... View more

Hi, Thank you for your quick response.   The lines are displayed in the block or line 14, if i need to add 20 values then all the values will be in row 14 instead of printed in 14 - 34.         But i am trying to get my out put something like this.       ... View more