11-26-2017 10:55 PM
The below code retreives blank value in ACCOUNT_NUMBER field. Not sure where its going wrong.
The variables which are not in ABC and available in DEF (ACCOUNT_NUMBER,UPDATE_WHEN_PMT_CD_AUTO_CHANGE etc) are blank . I want ACCOUNT_NUMBER to be populated. Kindly advise. Thanks.
proc sql noprint;
create table TEMP as
FROM ABC a left join (select *
FROM DEF b
where b.RATE_EFFECTIVE_DATE > ( select distinct reporting_date from ABC )
and b.reporting_date = ( select distinct reporting_date from ABC )
and UPCASE(strip(UPDATE_WHEN_PMT_CD_AUTO_CHANGE)) = 'N') c
on a.ACCT_NUMBER = c.Account_Number;
11-26-2017 11:37 PM - edited 11-26-2017 11:39 PM
I think your query should read:
proc sql; create table TEMP as select a.*, b.* FROM ABC as a left join ( select * from DEF where upcase(strip(UPDATE_WHEN_PMT_CD_AUTO_CHANGE)) = 'N') as b on a.ACCT_NUMBER = b.Account_Number and a.reporting_date = b.reporting_date and a.reporting_date < b.RATE_EFFECTIVE_DATE; quit;