Retreive blank value in left join

[Sandy10]

Hi.

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
select *
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;

quit;

[PGStats]

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;