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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: