Hi Ballardw Your solution worked perfectly. However I may have been mistaken with regard to how I want to handle other FIN_CD's separately from 1S. For example if FIN_CD output's anything other than '1S' then I want it to output blank or 'N'. This blank tells us we need to take a specific action. 1S means we have already did it. Basically this is an exception report. I need to combine the previous code you sugguested in addition to what I'm asking here. Hope this helps. ... View more

Thank you so much RW9.  I was able to separate TQ from the rest of the tran codes.  However what would be the solution to add trn_dt for TQ and the other trn_cd's?   Here it is now:   ACCT_NUM TRN_CD TRN_CODE_OTHER TRN_DT 123456789 TQ SF 15Dec2016     I would like it to show:   ACCT_NUM TRN_CD TRN_CODE_OTHER TRN_DT Diff_Between_TQ_and_TRN_CODE_OTHER(TRN_DT) 123456789 TQ SF 15Dec2016 OR   ACCT_NUM TRN_CD TRN_CODE_OTHER TQ(TRN_DATE) TRN_CODE_OTHER(TRN_DT) 123456789 TQ SF   The second option is fine since I can create a formula in excel to calculate the difference between dates. ... View more

Hi PG Stats   Sorry for the delay as I was out of the office due to the holiday.  I used your code but I received an error message   ERROR: File WORK.ACCT.DATA does not exist. ERROR: File WORK.ACCT.DATA does not exist.   The query did produce observations but it was the same as before.   ACCT_NUM TRN_CD TRN_DT 123456789 SF 15Dec2016 123456789 TQ 16Dec2016   Here's the entire code I used to run it:   PROC SQL; create table automation_exception as SELECT ACCT_DIM.ACCT_NUM, ACTVT_RSLT_FACT.TRN_CD, ACTVT_RSLT_FACT.TRN_DT, ACTVT_RSLT_FACT.TRN_NOTE, ACTVT_RSLT_FACT.USERID, STATUS_CD_LOOKUP.STATUS_CD, ACCT_DIM.LST_STATUS_CD, ACCT_DIM.LST_STATUS_CHG_DT, ACCT_DIM.DT_ASSIGNED, ACCT_DIM.ATTORNEY_CD, ATTORNEY_CD_LOOKUP.RECOVERER_CD_DESC, ACCT_DIM.ACCT_PURGED_FLG, TRANSACTION_FACT.TRN_CD FROM RDWP0.ACCT_DIM, RDWP0.ACTVT_RSLT_FACT, RDWP0.STATUS_CD_LOOKUP, RDWP0.TRANSACTION_FACT, RDWP0.RECOVERER_CD_LOOKUP ATTORNEY_CD_LOOKUP WHERE (ACCT_DIM.ACCT_ID=ACTVT_RSLT_FACT.ACCT_ID) AND ( STATUS_CD_LOOKUP.STATUS_CD=ACCT_DIM.STATUS_CD ) AND (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID) AND ( ATTORNEY_CD_LOOKUP.RECOVERER_CD=ACCT_DIM.ATTORNEY_CD ) AND ACTVT_RSLT_FACT.TRN_CD IN ( 'TQ', 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' ) AND ACTVT_RSLT_FACT.TRN_DT BETWEEN '1NOV2016'd and TODAY()-1 AND TRANSACTION_FACT.TRN_CD LIKE '1S'; SELECT tq.*, x.trn_cd as TranCodeOther, x.trn_dt as trn_date From acct as tq inner join acct as x on tq.acct_num=x.acct_num Where tq.trn_cd = "TQ" and x.trn_cd ne "TQ"; ; QUIT;   Thanks for your assistance. ... View more

  This is how it is now:   ACCT_NUM TRN_CD TRN_DT ************** TQ 12/16/2016   This is how I want it to display     ACCT_NUM TRN_CD_TQ TRN_DT Tran_Code_Other TRN_DATE ************ TQ 12/16/2016 *OTHER TRN_CODE *OTHR TRN_DATE         ... View more

Hi RW9 thank you for your quick response.  I'm learning the correct terminology already.  Here is some sample code:   WHERE (ACCT_DIM.ACCT_ID=ACTVT_RSLT_FACT.ACCT_ID) AND ( STATUS_CD_LOOKUP.STATUS_CD=ACCT_DIM.STATUS_CD ) AND (ACCT_DIM.ACCT_ID=TRANSACTION_FACT.ACCT_ID) AND ( ATTORNEY_CD_LOOKUP.RECOVERER_CD=ACCT_DIM.ATTORNEY_CD ) AND ACTVT_RSLT_FACT.TRN_CD IN ( 'TQ', 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' ) AND ACTVT_RSLT_FACT.TRN_DT BETWEEN '1NOV2016'd and TODAY()-1 AND TRANSACTION_FACT.TRN_CD LIKE '1S' ; run;   I need the report to pull tran code TQ first.  If TQ is found THEN I want to query tran codes 'SB', 'SD', 'SE', 'SF', 'SJ', 'SK', 'SL', 'SM', 'SO', 'SP', 'SS', 'SU', 'SV', 'SZ', 'TE' along with the tran_dt for TQ and the other codes.   Thanks for your help. ... View more