DATA Step, Macro, Functions and more

Help with proc sql variable equal

Reply
Occasional Contributor
Posts: 9

Help with proc sql variable equal

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

 

Super User
Posts: 7,824

Re: Help with proc sql variable equal

Posted in reply to jmoore168

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
---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
PROC Star
Posts: 7,480

Re: Help with proc sql variable equal

Posted in reply to jmoore168

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

 

Super User
Posts: 7,824

Re: Help with proc sql variable equal

Good catch, @art297!

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 3 replies
  • 123 views
  • 1 like
  • 3 in conversation