03-08-2017 05:48 PM - edited 03-09-2017 07:02 AM
I'm trying to output a status code that was on an account at the same time a specific note code (SQ) was placed. Here is the code I have:
PROC SQL; create table Putback_Report as SELECT ACCT_DIM.ACCT_NUM, ACTVT_RSLT_FACT.TRN_DT as REQ_DT, ACTVT_RSLT_FACT.TRN_CD as REQ_CD, ACCT_STATUS_HIST.STATUS_CD, ACCT_DIM.ATTORNEY_CD, ATTORNEY_CD_LOOKUP.RECOVERER_CD_DESC FROM RDWP0.ACCT_DIM, RDWP0.RECOVERER_CD_LOOKUP ATTORNEY_CD_LOOKUP, RDWP0.ACTVT_RSLT_FACT, RDWP0.ACCT_STATUS_HIST WHERE ( ACCT_DIM.ACCT_ID=ACTVT_RSLT_FACT.ACCT_ID ) AND (ATTORNEY_CD_LOOKUP.RECOVERER_CD=ACCT_DIM.ATTORNEY_CD) AND ACTVT_RSLT_FACT.TRN_CD = 'SQ' AND (ACCT_STATUS_HIST.STATUS_START_DT = ACTVT_RSLT_FACT.TRN_DT) AND (ACCT_STATUS_HIST.STATUS_END_DT = ACTVT_RSLT_FACT.TRN_DT) AND ACTVT_RSLT_FACT.TRN_DT BETWEEN '07JAN2017'd and '07JAN2017'd
The account status history table contains values for all status code history on an account and I need it to match with SQ. I've tried placing (the bold text) after the TRN_Dt, added parenthesis, added >= and <= (SAS stated couldn't recognize <>'s). All I want to do is give me the status code as of Jan 7, 2017.
The output I am getting appears to be a status code (A23) but cannot find it on the account history.
ACCT_NUM REQ_DT REQ_CD STATUS_CD 0000000000000000 07Jan2017 0:00:00 SQ A23
Can anyone confirm my syntax is correct?
03-09-2017 10:09 AM
Whether your syntax is correct depends on the underlying data, which we cannot see.
From the one piece of data you show, it looks like that REQ_DT is a datetime value, not a date value. Are you sure of whether all the variables you have are date and not date time and that you're comparing them correctly?