Help with proc sql variable equal

[jmoore168]

Hi!

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?

Thanks

[Kurt_Bremser]

First thing I'd do:

proc sql;
create table test as
select * from RDWP0.ACCT_STATUS_HIST
where STATUS_CD = 'A23'
;
quit;

Data doesn't appear out of nowhere.

Please post the log of your SQL; maybe there's a message there that you didn't recognize as important.

And

ACTVT_RSLT_FACT.TRN_DT BETWEEN '07JAN2017'd and '07JAN2017'd

is equal to

ACTVT_RSLT_FACT.TRN_DT = '07JAN2017'd

[art297]

Actually, I'm surprised that you're getting any records selected as your condition:

ACTVT_RSLT_FACT.TRN_DT BETWEEN '07JAN2017'd and '07JAN2017'd

appears to be comparing a datetime variable with a date.

Art, CEO, AnalystFinder.com

[Kurt_Bremser]

Good catch, @art297!