DATA Step, Macro, Functions and more

Retreive blank value in left join

Reply
Contributor
Posts: 39

Retreive blank value in left join

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;

 

Esteemed Advisor
Posts: 5,539

Re: Retreive blank value in left join

[ Edited ]

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;
PG
Ask a Question
Discussion stats
  • 1 reply
  • 106 views
  • 0 likes
  • 2 in conversation