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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.