Proc Sql = Setting two variables equal

jmoore168 — Thu, 09 Mar 2017 12:02:47 GMT

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

Re: Proc Sql = Setting two variables equal

collinelliot — Thu, 09 Mar 2017 15:09:24 GMT

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?

topic Re: Proc Sql = Setting two variables equal in SAS Programming

Proc Sql = Setting two variables equal

Re: Proc Sql = Setting two variables equal