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
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
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
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.